IF Formula, AND/OR

[Naval11]

Hi there

I have input three formulas in columns F, G, H of the attached workbook named 'Example1', but would like to include them all in just the one formula/column. Possible?

For completeness, what I am trying to achieve with the result is, if client pays within 35 days they receive 40% share of our commission (column E). More than 35 days they receive 30% share of our commission.

In calculating the 35 days, it is the Client Paid Date (column A) from the Eff. date (column C), UNLESS the Invoice Date (column B) is greater than the Eff. Date, then it will be Client Paid Date from the Invoice Date.

Hopefully makes sense, and thanks in advance.

[davsth]

firstly your dates are text, which is why i multiply by 1

=IF(1*A2-MAX(B2*1,C2*1)<=35,0.4,0.3)

[Naval11]

Thanks for your reply. Great the forumla works, to combine days and rate, but possible to combine the actual calculation and result of the commission based on the criteria?

Commission is a % of our commission (columnE)

[Roel Jongman]

Yes it is very easy to combine.

I made a simplifed version of your function as it has unneeded sum formulas

Please Login or Register  to view this content.

see attachement for result
note: you multiplied the date in B with the commission. so you prob wanred to use col E instead of col B but for context I first did the proces in steps first combined your current formulas then simplified it and in last step replaced B with E col this way you can also see the thought process.
I sometimes build complex formulas like in this way, start with separate columns of easy parts then look on how to combine them en when combined try if they can be simplified