How can formulas be used to create another formula?

G

Geag

This is probaby very simple, but I really don't know a lot aout formulars.

When I have created formulas to work out the percentage of each sales type
that we have, I then need to use this % as a part of another formula, Ie

Sales type A is 50% of our overall sales "=sum(B1/B3)*100", I am am then
trying use this 50% to apportion our wages bill to a sales type.

When I create another formula "=sum(B10/B13)*the cell reference of the
formula I previously calculated" it is throwing out the incorrect answer.

Can anyone give me a simple answer to a very simple question as I have
resorted to manually typing in the % excel has calculated and I am producing
very large and confusing spreadsheets?

Thanks very much
 
J

JE McGimpsey

Since you multiply your result by 100 to get percentages, you need to
divide by 100 when you use that result.

Another way to do it would be to simply format the percentage as a
percentage and use it directly. For instance

A1: 50
A2: 100

B1: =A2/A1

Format B1 as a percentage to display 50%. Note that SUM() is superfluous.

Then

C1: <total salary>
C2: =C1*B1

will display, in this case, 50% of the total salary in C2.
 
G

Geag

I have formatted the cell as a percentage, but even so it still is not giving
the right answer.

I have 3 sheets to my workbook, my first sheet gives all of the %'s and the
second is holding additional data. On my 3rd sheet I am quite simply using
the data on my 2nd sheet and multiplying it by the % in the 1st sheet.

To be honest it's that simple I can't understand what i'm doing wrong!
 
J

JE McGimpsey

You need to give more detail to be able for me to even make a guess.
What are your formulae?

This works for me:

Sheet1:

A1: 20
B1: 50
C1: =A1/B1 ===> 20% when formatted as a %age.


Sheet2:

J2: 150

Sheet3:

Z34: =Sheet1!C1 * Sheet2!J2 ===> 30
 
G

Geag

I have found out what it is, very simple - rounding!

Thank you very much for your help
 
Top