Using IF THEN ELSE in Excel 2007

Here, try this in I5 and pull up and down:

=(220-(COUNTIF($A$2:A5,A5)=COUNT($A$2:A5))*SUM($G$2:G4)-G5)/200

Originally Posted by zbor

Here, try this in I5 and pull up and down:

=(220-(COUNTIF($A$2:A5,A5)=COUNT($A$2:A5))*SUM($G$2:G4)-G5)/200

This is not working

May be if I attach the worksheet it will be helpful.

I need to include one more constraint. Once the sum of the values in column F becomes 11, for a particular value in column A, then I want it to stop calculating. That's what the logic has to be.

Hi Roger,
Thanks a lot for the suggestion. This may work for I4. But do you feel it would work for all the data cells in the column F?

Regards,
Anshul

Hi Roger,
I am including column F because it talks about the the number of resources. Anyways, that I can figure out. I think you're right. That shouldn't be included in the calculation. However, while calculating for I,
it should take into account all the previous values in column G if they have identical values in column A. .

Thanks & Regards,
Anshul

I'll make the explanation a little more clear. I need to do some calculation in column I depending on the values in column G and Column A. When the values in Column A changes, it should directly use the formula as specified in Else statement.
In other case, it should sum up all the values(current and previous data cell values) in column G for which the values in column A are identical. If the value in Column A change, it should use the Else formula.

I need it starting from the data cell I3.
Here's a rough explanation of the formula,
For I3:
IF(A3=A2) then (I3= (220-(G2+G3))/220) Else (I3=((220-G3)/220))

For I4:

IF(A4=A3 & A3=A2) then (I4= (220-(G2+G3+G4))/220) Else (I4=((220-G4)/220))

Similarly,
For I5:

IF(A5=A4 & A4=A3& A3=A2) then (I5= (220-(G2+G3+G4+G5))/220) Else (I5=((220-G5)/220))

and so on...

Could you please provide the correct syntax to use this logic?

Regards,
Anshul

And formula I write you above doesn't work? (Of coruse, if you put it where I write).

It worked now. I had by mistake used 200 instead of 220 at one place. Thanks a ton I really appreciate your help!

Roger, you could just change mine since it was already there
Was problem in first cell that SUM should not refer to itself (G2)

Also, I think it should refer to test F column if it's blank, not G.
That's because in H you'll have DIV/0 if F is blank so it would be nicer to see...

=IF(F2="","",(220-(COUNTIF($A$2:A2,A2)=COUNT($A$2:A2))*SUM($G1:G$1)-G2)/200)

Thanks,
I'm closing this thread.