Help with Formula

[martins]

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

[bj]

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
>
>

[martins]

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

[RagDyer]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[martins]

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