how to go about making this formula?

[bigdan]

I have a spreadsheet which I need to modify a bit with a much more complicated formula. Hoping someone can advise.


Here's the existing formula in C2.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Classification[/TD]
[TD]Revenue[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD][/TD]
[TD]Conv Date[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Business[/TD]
[TD]$500K[/TD]
[TD]if(ConvDate>C1,0,B2/12)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jan 15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Upsells[/TD]
[TD]$1M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Feb 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Essentially this takes the annual revenue, divides by 12 to get the monthly figures, then starts applying those monthly figures after the conversion date. So far simple enough.


There's a new complication now. If the Classification in col A is "Transaction" we have to recognize the entire revenue in col B in a single month, whatever that date is in the Conv date. So that means in a row which has a Transaction classification, rather than seeing multiple equal revenue figures we'll instead see 11 months with nothing and 1 month with the entire annual revenue.

Can someone advise how I'd write the formula, that would still treat all the other Classifications the same (ie dividing by 12 and distributing after the Conv Date) and would just treat the Transaction class differently?

 

[LouisT]

Hey Dan, i would just use a qualifying "IF" statement to recognize if the Classification is "Transaction" then do X, else, do Y.

it would be something like: =IF($A3="Transaction",IF(MONTH($H3)=MONTH(C$1),$B3,0),IF($H3>C$1,0,$B3/12))

Hope that helps.
LouisT