SUM and IF formula

[EstherJ]

In the following table I need the values in row 5 to be 30 or below. The
current formula in cell F5 for example is =SUM(F3/E2)*30. I need to change
this formula so that if the result is over 30 it will go back and try
=sum(F3/sum(D2:E2))*30 and then =sum(f3/sum(C2:E2))*30 and so on until the
result is 30 or below. I'm not sure how to construct it.

Thanks,

Esther



X A B C D E F
1 Mar Apr May Jun Jul Aug
2 1,169.98 773.46 773.20 1,005.25 823.27 773.19
3 851.00 1,081.32 1,000.59 594.01 754.51 926.16
4
5 27.73 38.81 23.05 22.52 33.75

 

[mrice]

A user defined function can help here

Function Special(Cell1 As Range, Cell2 As Range)
Do
TotalOfCell2Row = 0
N = N + 1
For M = 1 To N
TotalOfCell2Row = TotalOfCell2Row + Cell2.Offset(0, 1 - M)
Next M
Special = Cell1 / TotalOfCell2Row
If Special < 1 Then
Special = Special * 30
Exit Function
End If
Loop
End Function

To use this, set the value of Cell1 = F3 and Cell2 = E2