Can anyone help?
I have a 4 column / 7row spreadsheet which at present sums a formula in
cell C2 which calculates that IF A2<10%,0,D2-B2) – however, what I
actually want the formula to do is as follows-

If A2 <10% and cell B2 is <0 return 0 in cell C2
However IF A2 is 10% then calculate the difference between D2-B2 (or
10% of cell D7) whichever is the higher and return answer in cell C2

Example where A2 is Greater Than 10%:
IF A2 is 10% and B2 =40 and D7=50 (based on 10%) then the value over
limit (c2) would be 50. However if B2 = 60 then the value over would
be 40.

Hope this doesnt sound too confusing


--
martins
------------------------------------------------------------------------
martins's Profile: http://www.excelforum.com/member.php...o&userid=31616
View this thread: http://www.excelforum.com/showthread...hreadid=572846

try in C2
=if(and(A2<10%,B2<0),0,Max(D2-B2,0.1*D7))

"martins" wrote:


Can anyone help?
I have a 4 column / 7row spreadsheet which at present sums a formula in
cell C2 which calculates that IF A2<10%,0,D2-B2) €“ however, what I
actually want the formula to do is as follows-

If A2 <10% and cell B2 is <0 return 0 in cell C2
However IF A2 is 10% then calculate the difference between D2-B2 (or
10% of cell D7) whichever is the higher and return answer in cell C2

Example where A2 is Greater Than 10%:
IF A2 is 10% and B2 =40 and D7=50 (based on 10%) then the value over
limit (c2) would be 50. However if B2 = 60 then the value over would
be 40.

Hope this doesnt sound too confusing


--
martins
------------------------------------------------------------------------
martins's Profile: http://www.excelforum.com/member.php...o&userid=31616
View this thread: http://www.excelforum.com/showthread...hreadid=572846

Thank you for your reply - I appreciate that my post is probably
sounding a bit confusing - your reply nearly worked but did not provide
the result I was looking for - I will try to explain further -

In the second part of the equation i.e. where A2 is 10% I am looking
to return a result in C2 based on D2 less the value of cell B2 or 10%
of cell D7 (whichever is the higher of the 2 cells) -

Dont know if this helps to make clearer


--
martins
------------------------------------------------------------------------
martins's Profile: http://www.excelforum.com/member.php...o&userid=31616
View this thread: http://www.excelforum.com/showthread...hreadid=572846

The formula bj suggested works *exactly* as you asked!

In your example in your OP, you never stated what value was in D2.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"martins" wrote in
message ...

Thank you for your reply - I appreciate that my post is probably
sounding a bit confusing - your reply nearly worked but did not provide
the result I was looking for - I will try to explain further -

In the second part of the equation i.e. where A2 is 10% I am looking
to return a result in C2 based on D2 less the value of cell B2 or 10%
of cell D7 (whichever is the higher of the 2 cells) -

Dont know if this helps to make clearer


--
martins
------------------------------------------------------------------------
martins's Profile:
http://www.excelforum.com/member.php...o&userid=31616
View this thread: http://www.excelforum.com/showthread...hreadid=572846

The cell D2 sums the total of cells E2 to H2 -

Sorry if I am not putting this across properly but in the second part
of the formula I am trying to obtain a result based on comparison to
cell b2 or d7 which is ever is highest - cell D7 sums the total of
Cells d2 to d6 -

Thanks


--
martins
------------------------------------------------------------------------
martins's Profile: http://www.excelforum.com/member.php...o&userid=31616
View this thread: http://www.excelforum.com/showthread...hreadid=572846