Excel 2000 Newbie Question-Formulas

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

cruachaun

Thread Starter
Joined
Sep 8, 2003
Messages
9
I have a spreadsheet where a number is entered each month ( central
heating gas consumption ) and I then need to employ the following
operations to that number each month :-

1.the number in the cell ( A1 say ) to be multiplied by 11.4612

2.the answer to this to be further multplied by 1.9757

3.the answer arrived at in step 2 to be multiplied by 12.5%

4.the answer to step 3 to be added to the answer arrived in step 2

All of these operations to be applied each month as a new number is
added in cells B1, C1 ...etc each month of the year. ( These numbers
represent monthly gas consumption in cu.meters)

I would also like to keep a running total of the figure arrived in
step 4 after each nonths entry.

Can all these calculations be done by use of a formula ?

If so , how do I construct a suitable formula , please ?
 
Joined
Aug 30, 2003
Messages
2,702
You don't say if this is a megaformula, or if you need to keep the steps. Assuming the latter ...

=A1*11.4612 entered in A2.
=A2*1.9757 entered in A3.
=A3*12.5 entered in A4.
=A3+A4 entered in A5.
=SUM(5:5) entered in A7 (running total).

You can select A2:A5 and drag across using the fill handle (small black square at the bottom right of the selection).

HTH,
Andy
 
Joined
May 4, 2002
Messages
743
XLG's question is a good one, Cru - Do you need to see the intermediate numbers? If so, then follow the steps he listed. If not, then perhaps consider simplifying it into one expression.

In A2 you would have everything:

=A1*11.4612 *1.9757 *1.125
I made an assumption that you wanted to find 12.5 percent of the number in A3. Then since you added it back to A3 I made the assumption that you ultimately wanted to see 1.125% of that number - hence I merely multiplied it by 1.125. That's a lot of assumptions, I know, and so if I missed the mark you can adjust it all accordingly

You wouldn't see any of the interum numbers - just the result.
Do as XLG says and drag the formulas out to the right. Every column would have the monthly number in ROW 2

somewhere in column A insert his =sum(2:2) and it would give the running total

if the factors 11.4612 & 1.9757 are subject to change in the future you may consider placing them in their own cells and having the formula reference them.

If though you need to see the intermediate numbers as they are calculated then you may employ XLG's work. He will be sure to remind me of that fact in the morning! :D

- Castleheart
 
Joined
Aug 30, 2003
Messages
2,702
>> He will be sure to remind me of that fact in the morning!

Not at all -- in fact, I'd not long logged off before I realised I'd completely forgotten to mention that it's generally considered best practice to keep factors separate from the actual formulas, to make upgrading them in future easier, if necessary. Nice 1, CH.

Rgds,
Andy
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,746
It's also easier to give those "factors" a named range. So type those values in cells. Select the first value and Insert-Name-Define and call it (for instance) Factor1 or StateTax or something descriptive like that.

Then you can change CH's formula to something like this:

=A1*StateTax *UtilityTax * UseTax

What they're saying is you can then change the value you named StateTax from 11.4612 to 11.4709 by changing just one cell instead of all the formulas.
 

cruachaun

Thread Starter
Joined
Sep 8, 2003
Messages
9
Thanks to all who've replied . Now that I've got the general idea I'll be able to experiment and get the results the way I want.

Much obliged.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top