I have been working on this for days, and as I am not an Excel expert I just can't seem to get it to do what I want. Here is what I'm trying to do....
1.find out if the total value of Column C cells to this point is under, equal or over 250.00.
2.When the value of the total of the C cells exceeds 250.00 take the remainder that is over 250.00 and divide by 2. put the sum in cell D. If the result is = or less than 250.00 the value should read $0.00 in cell D (Not FALSE)
3. For each cell value after the cell that has exceeded the 250.00 mark take ONLY the value of cell C and divide by 2 and place the sum in Cell D
I'm sure i'm so far off track here but this is what I did (Amateurish I know) =IF(OR(C4+C5+C6+C7+C8+C9+C10+C11+C12+C13=250,C4+C5+C6+C7+C8+C9+C10+C11+C12+C13<250),"$0.00",SUM(C4+C5+C6+C7+C8+C9+C10+C11+C12+C13-250)/2)
The problem is I don't know how to automatically decide if the value in C is over, under, or equal AND if just part of the value of the C cell is over, or all of the value is over. For example, on row 13 in my spreadsheet the value exceeds 250.00 for cells C4-C13 by 3.00. the 3.00 then gets divided by 2 and the sum is placed in Cell D. Since I've already exceeded 250.00 the all of the value of any cells after 13 would exceed 250.00 and just that cell value would be divided by 2 and the sum placed in cell D.
Yes I couldn't figure out how to get the sum calculation of all the cells in the column without adding each one individually.
Thanks in advance for any assistance - this is a potentially good teaching moment for me
Rich
Bookmarks