# Excel 2000 Newbie Question-Formulas

Discussion in 'Business Applications' started by cruachaun, Sep 12, 2003.

Not open for further replies.

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%

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 ?

2. ### XL Guru

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

3. ### CastleHeart

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!

- Castleheart

4. ### XL Guru

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

5. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
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.

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.

As Seen On