How to combine two formulas into one?

[honkin]

I have 2 separate lines of code which both work fine, but I need to have them work together.

Here is the first line:

=IF(F19="","",IF(F19<3.5,L18*0.01,IF(F19<7.45,L18*0.006,IF(F19<11.1,L18*0.004,"0"))))

Here is the second line of code:

=IF(F19="","",IF(F19<3.5,$M$18*0.01,IF(F19<7.45,$M$18*0.006,IF(F19<11.1,$M$18*0.004,"0"))))

I need them each to be used depending on this criteria - IF L19>$M$18 I want the first formula used, but IF L19<=$M$18, I want the second formula used.

Writing each line separately wasn't such an issue, but for the life of me, I always get lost trying to combine a couple of functions like this.

Here is what I tried, but Excel tells me there are too many arguments

=IF(L19>$M$18,IF(F19="","",IF(F19<3.5,L18*0.01,IF(F19<7.45,L18*0.006,IF(F19<11.1,L18*0.004,"0",IF(L19<=$M$18,IF(F19="","",IF(F19<3.5,$M$18*0.01,IF(F19<7.45,$M$18*0.006,IF(F19<11.1,$M$18*0.004,"0"))))))))))

Any thoughts how to have it work as described above?

cheers

 

[diddi]

=IF(L19>$M$18,IF(F19="","",IF(F19<3.5,L18*0.01,IF(F19<7.45,L18*0.006,IF(F19<11.1,L18*0.004,"0")))),IF(F19="","",IF(F19<3.5,$M$18*0.01,IF(F19<7.45,$M$18*0.006,IF(F19<11.1,$M$18*0.004,"0")))))

 

[honkin]

=IF(L19>$M$18,IF(F19="","",IF(F19<3.5,L18*0.01,IF(F19<7.45,L18*0.006,IF(F19<11.1,L18*0.004,"0")))),IF(F19="","",IF(F19<3.5,$M$18*0.01,IF(F19<7.45,$M$18*0.006,IF(F19<11.1,$M$18*0.004,"0")))))

Hi diddi

Thanks for your reply

Apologies for that. With it not being a VBA macro, I wouldn't have been sure where to indent, but I'll try and have it on multiple lines if it helps

=IF(L19>$M$18,IF(F19="","",IF(F19<3.5,L18*0.01,IF(F19<7.45,L18*0.006,IF(F19<11.1,L18*0.004,"0",
IF(L19<=$M$18,IF(F19="","",IF(F19<3.5,$M$18*0.01,IF(F19<7.45,$M$18*0.006,IF(F19<11.1,$M$18*0.004,"0"))))))))))

I hope this makes it easier to see the quite long line

cheers

 

[diddi]

i posted the correction previously

 

[Peter_SSs]

See if this more compact form does what you want. I believe that it produces the same results as the post #2 formula (except I have assumed it acceptable, possibly preferred, that this returns the number 0 rather than the text "0" when those conditions are met)

=IF(F19="","",LOOKUP(F19,{0,3.5,7.45,11.1},{0.01,0.006,0.004,0})*IF(L19>$M$18,L18,$M$18))

BTW, note that the forum has specific tags for worksheet formulas now.

 

[honkin]

i posted the correction previously

Thanks for your reply diddi. That code handles L19>$M$18 only. It does not do anything for L19<=$M$18
cheers

 

[honkin]

See if this more compact form does what you want. I believe that it produces the same results as the post #2 formula (except I have assumed it acceptable, possibly preferred, that this returns the number 0 rather than the text "0" when those conditions are met)

=IF(F19="","",LOOKUP(F19,{0,3.5,7.45,11.1},{0.01,0.006,0.004,0})*IF(L19>$M$18,L18,$M$18))

BTW, note that the forum has specific tags for worksheet formulas now.
View attachment 31522

Thanks for your reply

This code only looks at the condition L19>$M$18 It does not include L19<=$M$18

In the end, this was the code which combined it all perfectly
=IF(L18>$M$18, IF(AND(F19="",L18>$M$18),"",IF(F19<3.5,L18*0.01,IF(F19<7.45,L18*0.006,IF(F20<11.1,L18*0.004,"0")))), IF(L18<=$M$18, IF(AND(F19="",L18<=$M$18),"",IF(F19<3.5,$M$18*0.01,IF(F19<7.45,$M$18*0.006,IF(F19<11.1,$M$18*0.004,"0")))), ""))

Thanks again

 

[jtakw]

Hi,

Just want to point out:

Thanks for your reply diddi. That code handles L19>$M$18 only. It does not do anything for L19<=$M$18
cheers

Thanks for your reply

This code only looks at the condition L19>$M$18 It does not include L19<=$M$18

Thanks again

Both of these suggestions will work, the formula does Not need to test Both L19>$M$18 and L19<=$M$18,
You Only need to test for 1 of those conditions, if L19 is > M18, then obviously, L19 can Not be <= M18, or vice versa.

 

[Peter_SSs]

In the end, this was the code which combined it all perfectly

=IF(L18>$M$18, IF(AND(F19="",L18>$M$18),"",IF(F19<3.5,L18*0.01,IF(F19<7.45,L18*0.006,IF(F20<11.1,L18*0.004,"0")))), IF(L18<=$M$18, IF(AND(F19="",L18<=$M$18),"",IF(F19<3.5,$M$18*0.01,IF(F19<7.45,$M$18*0.006,IF(F19<11.1,$M$18*0.004,"0")))), ""))

Are you sure that formula is doing what you want?
If so, then your original question was not correct because in post #1 you were comparing L19 to $M$18 but above you are comparing L18 to $M$18

IF L19>$M$18 I want the first formula used, but IF L19<=$M$18,

 

[honkin]

Are you sure that formula is doing what you want?
If so, then your original question was not correct because in post #1 you were comparing L19 to $M$18 but above you are comparing L18 to $M$18

Cheers Peter, a typo only. In the end, the row for L will change as it is copied down; the only absolute being $M$18. L19 was just the row I copied the original from. The data in this sheet only starts in row 18. The original question was correct and was unanswered. I found the correct formula and posted it back here in case it helped anyone else

cheers