Embedded IF statement placements

cdnqte

Board Regular
Joined
Jul 14, 2004
Messages
132
Hi all!

I am writing a report that is calculating deferred/accrued revenue to be accounted for each month. To determine how many days are allowed for deferral I have a column that tells me how many days are in each period. There are 12 buckets as there are 12 billing periods.

To look up to that information in my report I have a formula that says =IF([@[Billing Cycle ]]="LT",SUM(MONTH([@[Last Bill date]])+1),IF([@[Billing Cycle ]]="LD",SUM(MONTH([@[Last Bill date]]+1)),MONTH([@[Last Bill date]]))) which is all good UNLESSS the last bill date is December because if it is it is trying to make it 13 when I only have 12 periods a year.

I tried adding another if statement that says if(month@[Next Bill Date]=12,1,IF([@[Billing Cycle ]]="LT",SUM(MONTH([@[Last Bill date]])+1),IF([@[Billing Cycle ]]="LD",SUM(MONTH([@[Last Bill date]]+1)),MONTH([@[Last Bill date]])))

I must be putting it in the wrong spot as it is not working. All the embedded ifs trip me up every time! If someone could help that would be greatly appreciated!
 
Last edited:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
what happens if it is December? does it create an error if so could you not wrap the formula in an if error statement?
 
Upvote 0
I ended up figuring it out, I had it in the wrong spot. This format did the trick:

=IF(MONTH([Last Bill date])=12,1,(IF([@[Billing Cycle ]]="LT",SUM(MONTH([@[Last Bill date]])+1),IF([@[Billing Cycle ]]="LD",SUM(MONTH([@[Last Bill date]]+1)),MONTH([@[Last Bill date]])))))
 
Upvote 0
Assuming the +1 issue only arises for LT cycles, I think this should work:

=IF([@[Billing Cycle ]]="LT",MOD(MONTH([@[Last Bill date]]),12)+1,IF([@[Billing Cycle ]]="LD",MONTH([@[Last Bill date]]+1),MONTH([@[Last Bill date]])))
 
Last edited:
Upvote 0
=IF(MONTH([@[Last Bill Date]])+IF(OR([@[Billing Cycle]]="LT",[@[Billing Cycle]]="LD"),1,0)>12,1,MONTH([@[Last Bill Date]])+IF(OR([@[Billing Cycle]]="LT",[@[Billing Cycle]]="LD"),1,0))
 
Last edited:
Upvote 0
In the original formula, the calculations for LT and LD are different, but I don't know if that was deliberate or not.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top