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!

 

[Legacy 396030]

Try to use the Jeannie Add In to post the sheet range.

 

[philaugust2004]

very hard to see but:
Why do you have =c2 at the end of your first formula?
2nd formula in A7 had reference to D7 when I think it should be E7. You also only have a TRUE value which is U7 (but hard to see so could be something else).
To try and solve
in A2 remove =c2
in A7 try this
=if(and(b7<=d7,E7>1),b7,d7)

 

[Surreybloke]

Thanks for coming back to me.

The end of the formula in A2 is actually +C2, which is to add the cancellation value (normally zero but will sometimes have a negative value) to the commissionable value.

In my table I should have put 200 in the contract item value column and 100 in the annualized value column as this would be a true reflection of how the data would come through. Your formula gave me the lower value rather than the higher value for this particular row.

 

[Surreybloke]

Try to use the Jeannie Add In to post the sheet range.

Apologies for my ignorance, I've not heard of the Jeannie add-in before.

 

[Surreybloke]

I've just worked it out... by flipping the argument around to ensure it still gives me the lower value if the quantity is 1, but giving me the higher value if the quantity is more than 1. Thanks for your help!

=IF(AND(B7>=D7,E7>1),B7,D7)+C7

 

[Surreybloke]

I've found I haven't quite got it solved after some further testing. Please see below... The bottom two rows are the same but with one displaying the formula. I'm find it returns 100 rather than 50, which is what I need it to show. I don't understand why the AND argument isn't picking up when it needs to return the other value where the quantity is 1.

[TABLE="width: 724"]
<tbody>[TR]
[TD]Commissionable Value[/TD]
[TD]Contract Item Value[/TD]
[TD]Cancellation Value[/TD]
[TD]Annualized Value[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]=IF(B2<=D2,B2,D2)+C2[/TD]
[TD="align: right"]5,187.00[/TD]
[TD="align: right"]-5,187.00[/TD]
[TD="align: right"]1,000.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]0.00[/TD]
[TD="align: right"]3,650.00[/TD]
[TD="align: right"]-3,650.00[/TD]
[TD="align: right"]3,650.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]0.00[/TD]
[TD="align: right"]176.76[/TD]
[TD="align: right"]-176.76[/TD]
[TD="align: right"]176.74[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]0.00[/TD]
[TD="align: right"]7,069.00[/TD]
[TD="align: right"]-7,069.00[/TD]
[TD="align: right"]7,069.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]0.00[/TD]
[TD="align: right"]3,356.00[/TD]
[TD="align: right"]-3,356.00[/TD]
[TD="align: right"]3,356.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]100.00[/TD]
[TD="align: right"]800.00[/TD]
[TD="align: right"]-300.00[/TD]
[TD="align: right"]400.00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]200.00[/TD]
[TD="align: right"]200.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]100.00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]224.98[/TD]
[TD="align: right"]500.00[/TD]
[TD="align: right"]-275.00[/TD]
[TD="align: right"]499.98[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]=IF(AND(B10>=D10,E10>1),B10,D10)+C10[/TD]
[TD="align: right"]50.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]100.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]100.00[/TD]
[TD="align: right"]50.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]100.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

 

[Rorylfc]

The formula is returning 100 as both parts of the AND are not true.

In the formula above, assuming I am reading it correctly....
B10>=D10 will result in FALSE as 50 is not greater than 100
E10>1 will result in False as the value in E10 (1) is not greater than 1.

Due to both of these being false, the answer given is D10 (100)

 

[philaugust2004]

Are you sure none of the numbers are formatted as text?

 

[Surreybloke]

Are you sure none of the numbers are formatted as text?

Yeah I'm sure - I manually added the numbers myself for the last few rows after copying and pasting some data from my actual spreadsheet onto a new one for the purposes of putting the information on here.