Problem using IF and AND together in same formula

[Surreybloke]

Hi,

I hope the image above isn't too small. The browser crashed when I tried pasting the section of Excel directly.

You will see above that there are two different formulae - one that is simply asking the commissionable value column to return the lowest number between columns B and D, and to then add the value in column C. The formulae in cell A7 is trying to improve on this by asking for the value which is lowest and where the quantity is more than 1.

The reason why I need the lowest value (unless the quantity is more than 1) is because some of the totals are annualized where the contract length is greater than 12 months which produces a lower value than the contract item value and sometimes the contract length is less than 12 months which means I need to return the contract item value. This changes though if the quantity is more than 1. If that is the case, then the contract item value would always be brought through.

I'm hoping someone can help me with the correct formula so that it performs in the intended way.

Many thanks!

 

[Surreybloke]

If I change the formula to =IF(AND(B10>=D10,E10>1),B10,D10)+C10 by flipping around the parts of the formula marked in red, it brings through the lowest value, but won't bring through the higher value when the quantity is more than 1.

In layman's terms, what I need it to do is if the quantity is 1, and the annualized value is lower, bring that through. If the contract item value is lower, bring that through instead. If the quantity is 2 or more, I want it to bring through the higher value, which would be the contract item value. (the annualized value being the 12 month value based on a quantity of 1)

 

[philaugust2004]

with formula as it is now it will return b10 if b10 is greater than d10 AND e10 is greater than 1.
It returns d10 if either or both those conditions are false

 

[Surreybloke]

What do you think I should amend it to? I've tried a few permutations but haven't hit upon the right combination.

 

[philaugust2004]

needs nested IF but can't look until later

 

[philaugust2004]

deleted<b2),d2,if(and(e2></b2),d2,if(and(e2>

 

[philaugust2004]

try this and copy down (remove spaces)

=IF(AND(E2=1,D2 < B2),D2,IF(AND(E2 > =2,D2 > B2),D2,B2))+C2

 

[Surreybloke]

That looks like the answer - many thanks for your help

 

[philaugust2004]

glad to help even though I had temporary brain freeze!

 

[Surreybloke]

It's mandatory to get one or two of them each day!