Nested if functions help

[ashley22]

Hi,

I am trying to use a number of nested if functions to return a total number based on the number of weeks the cash flow will impact e.g. 5 weeks with cash flow of £50,000 is £250,000. The complexity I have is how to spread this out into quarters across the year based on user specified start and end dates e.g.

• Q1 is 201401 to 201413 (inclusive) if the cash lands in the final 2 weeks in Q1 (201412 to 201413 inclusive) and the remaining 3 weeks in Q2 (201414 to 201416 inclusive) I want the result to show £100,000 for Q1 and £150,000 for Q2.
The other complexity I have is if I have a one off cheque payment I add this into a separate column and want Excel to ignore the cash column and look at the cheque column while putting the correct value into the relevant Quarter column e.g.
• If I have a cheque banked in Q3 (201427 to 201439) I want the result to be out into the Q3 column and not multiplied by the number of weeks.
So far I have the following formulas:

Q1 - =IF($C7>=201414,0,IF($B7="Cheque",$L7,$E7*(201414-$C7)))
Q2 - =IF($B7="cheque",IF($C7>=201414,$L7,0),$E7*(201427-MAX($C7,201414)))
Q3 - =IF($B7="cheque",IF($C7>=201427,$L7,0),$E7*(201440-MAX($C7,201427)))
Q4 - =IF($B7="Cheque",IF($C7>=201427,$L7,0),$E7*(201440-MAX($C7,201427)))

I have also attached an example document which may be helpful.

I hope this is clear to all.

Many thanks

[k64]

Hi ashley22,
I'm not sure I understand what you're trying to do. Can you update your spreadsheet with an example of what the result should be and why?

[ashley22]

Multiple nested if function help.xlsxHi,

Apologies that this was not clear, it is difficult to explain. I have added some additional examples in row 13 to 15 and added my comments in column M which explains the result.

Hope this is clearer.

[k64]

Is this what you wanted?

[ashley22]

Hi,

Thank you for the reply. It is almost there! The calculation works perfectly if the type is seelcted as 'Cash' however the 'Cheque' option is not working. In row 9 on the example £900,000 should show up in only Q2 and in row 14 £200,000 should show up in Q4.

Thanks

[FDibbins]

This would probably be much easier if you used actual dates. Excel has a WEEKNUM() function that determines the week number. Using that, we could probably come up with a more effective formula

[k64]

Like this?

Edit: I agree with FDibbins above.