Nested IF functions?

[Tea Boy Sid]

Hi Everyone

I'm trying to work out the best way of Achieving a minimum figure (of £11.90) in column H of the attached spreadsheet. I know I can do this with an IF function, but I already have the result of another IF function in that column. In Column D, I can pick either Wholesale or Trade and the results either go in column G or H depending on the choice.

A bit of background! The figures are derived from the totals in Column F but if this figure falls below £79.56, a minimum send out fee of 11.90 applies. This is the figure I am having trouble with. This only applies to the Trade 15% column not the wholesale 6%.

Thanks in advance.

[tcholton]

Under your current "value if true", you can add a nested if that checks the result, and changes if necessary.

=IF(D:D="Trade 15%",[@[Invoice Total]]*15%,0)

[@[Invoice Total]]*15%

This is the portion you want to check, so change it into:

if([@[Invoice Total]]*15%<11.90,11.90,[@[Invoice Total]]*15%)

Leaving you with

=IF(D:D="Trade 15%",if([@[Invoice Total]]*15%<11.90,11.90,[@[Invoice Total]]*15%),0)

Which will give you what you want.

edit: pjwhitfield did give a more succinct, easier answer below. above should be used as a lesson in nested ifs for future reference

[pjwhitfield]

Can you provide a bit more info, are you saying that the UK 15% (col H) figure should be at least £11.90 if its a trade sale?

ie if the total is £50 then 15% would be £7.50 but you want it to be £11.90?

£75 would be £11.25 which again should become £11.90 while,

£80 would calculate £12 which is fine?

[pjwhitfield]

Under your current "value if true", you can add a nested if that checks the result, and changes if necessary.

=IF(D:D="Trade 15%",[@[Invoice Total]]*15%,0)

[@[Invoice Total]]*15%

This is the portion you want to check, so change it into:

if([@[Invoice Total]]*15%<11.90,11.90,[@[Invoice Total]]*15%)

Leaving you with

=IF(D:D="Trade 15%",if([@[Invoice Total]]*15%<11.90,11.90,[@[Invoice Total]]*15%),0)

Which will give you what you want.

or alternatively, without another IF

Please Login or Register  to view this content.