Nested Formula - I think...

[GarySmith]

New member here, I hope you guys can help?

I have a workbook called Contractor's Bid.

The workbook consists of several worksheets with Tab names.

To keep my question as simple as possible, I want to set a default profit margin on a sheet called "Admin". I can now reference the profit margin % [as a value] into several worksheet formulas and by simply changing the admin sheet cell (that's dedicated to profit, let's say the address of that cell is =Admin!D1), then all worksheet(s) are calculated and they are all calculated with the same profit margin [the data on the Admin sheet in cell D1]. However, there may be times (on specific worksheets) when I don't want to use the "default profit margin".

Can I dedicate a cell on each worksheet and create a conditional statement (if statement?) that says: if(G1>=1) then multiply the sum in G1 rather then the sum of my "default profit margin" on the Admin sheet?

Does that make sense? I hope I am making my question clear?

 

[s hal]

You would put something like the following into any cells that need one or the other value:

if($G$1>=1,$G$1,Admin!$D$1)

 

[GarySmith]

That one didn't quite make it...?

So, I have this [where C1, in the formula below, is: =Admin!D1 is set to 10%]

Column A=Qty=1
Column B=Cost=100
Column C=Profit=Admin!D1
Column G=Custom Profit={some number greater than 0}
************

I am now successfully rendering a correct value for this formula on any cell in the worksheet: =Sum(A1*B1*C1)
The formula will return the value: $10 [1 widget X $100 = $100 X =Admin!D1 = $10]

Can you help with the if statement to return a custom profit margin when and if I enter some value in column G1 is set to, say 20%, but if G1=0, then the default value [=Admin!D1] will be used?

 

[s hal]

In Place of C1 in your formula, you would put what I have above. Alternatively, since you seem to be including it in both places on all your sheets, you could change your formula to the following:

=SUM(A1*B1*if(G1>=1,G1,C1))

That said, what I think you are ACTUALLY looking for is when G1 is a non zero value to use that otherwise use a default, in which case you'll want the following:

=SUM(A1*B1*if(G1<>0,G1,C1))

 

[GarySmith]

Brilliant! I used the second scenario: =SUM(A1*B1*if(G1<>0,G1,C1))

Saying: thank you, thank you!!

 

[s hal]

Glad it worked for you

 

[GarySmith]

I actually thought it was off by a factor of 100, but was only because I didn't have the cell formatted to %!

 

[Aladin Akyurek]

No need for SUM around though.