Need a Calculation Formula

Question

In Excel, I'm trying to write a formula that will find the difference greater than a specific and number.  Example a formula is needed for E1:

On Worksheet A:

C1= 500   D1= 500   E1 =??? (Formula Needed)     

On Worksheet B:

 A1= 500   B1= 500   C1= 500   D1 =500

IF(SUM('B'!A1:D1)>1000 then I need a formula for  E1 of Worksheet A to equal "500" only.  TheSUM('B'!C1:E1) on Worksheet A can't exceed 1,500.

Hope my question is clear.

Thanks,

RAE

Answer 1

See below:

Sub budget worksheet B (this sheet feeds into primary budget):

YR1        YR2          YR3        YR4       YR5

C1          D1           E1           F1          G1                   

10,000    5,000      5,000       2,000      10,000            C1:G1=32,000   (25,000 of this is subject to

                                                                                  additional  fees)

Primary budget  worksheet A (has several sub budgets feeding into it).  There is a specific $$$ amount of each sub's budget that are subject to additional fees by the primary. The MAX for all years is $25K.

G1 of Year 5 calculates the portion of the subs costs subject to the prime's fees up to 25,000.  In this example Y5 G1 should equal 3,000 and Y5 G2 will equal 7000.  So the sum of G1 and G2 will still equal 10,000. 

YR1         YR2         YR3       YR4       YR5

C1           D1          E1          F1            G1

                                                            3,000

C2            D2         E2          F2            G2

                                                            7,000

Answer 2

So isn't that just the sum - 1000?

=IF(SUM(B!A1:D1)>1000,SUM(A!C1:D1)-1000,0)

??

Cheers

Rich

Answer 3

Hi,

I'm still not sure exactly what you are SUMMING so change the sum range in this to the correct one.

=MAX(0,SUM(D1:E1)-1000)

Answer 4

My apologies for the ambiguity.  I would like E1 to automatically calculate the value that exceeds 1,000.  Since the values in each of these cells can vary.  For instance the sum of the  value of the cells could be 1,300 in this case E1 should return the value "300".  If the sum were 1,080 E1 should return the value "80".

Thank you!

Answer 5

Hi,

Something like this:

=IF(SUM(B!A1:D1)>1000,1500-SUM(A!C1:D1),0)

"Hope my question is clear"... unfortunately it isn't.  What do you want to appear in E1 if SUM(B!A1:D1)<= (less than or equal to) 1000?  In the above formula, it would return zero in this case.

Hope that helps.

Cheers

Rich