IF formula with multiple dependencies

**jomili** · 12-07-2011, 01:43 PM

I've provided a picture rather than a file, but I think the picture will suffice for this question. The highlighted area in L is the average of Columns D,E,F (June, July, and August) for that line

In column K, the formula looks at the average of June, July, and August, and if it's greater than the sum of September, October, and November, then the average is multipled by 12 for our projection, otherwise the sum is used (should be sum *4; I have an error in my formula)

I need to change my formula to the situation below, but am drawing a blank on how to do it:

IF ONLY September is greater than the average, use September + the avg * 11
IF ONLY October is greater than the average, use October + the avg * 11
IF ONLY November is greater than the average, use November + the avg * 11
IF September AND October are greater than the average, use September + October + the avg * 10
IF Sept, Oct, and Nov are greater than the average, use Sept, Oct, and Nov + the avg * 9

I'm envisioning about a 4 foot long IF statement, and really hope there's an easier way to do it that one of you Excel gurus can help me with.

Thanks in advance,
John

**tigeravatar** · 12-07-2011, 02:00 PM

Give this a try:
=SUMPRODUCT(--(G4:I4>AVERAGE(D4:F4)),G4:I4)+AVERAGE(D4:F4)*(12-COUNTIF(G4:I4,">"&AVERAGE(D4:F4)))

**jomili** · 12-07-2011, 02:42 PM

~tigeravatar,

Thank you so much! The formula works beautifully. The only change I had to make was to add a parentheses. Thank you, thank you, thank you!