lcor
asked on
EXCEL Running Sum Based on Column Values
I have 5 columns with possible values A, B, C, each will either add a point or subtract a point from a running sum based on value of the cell. The 6th column will be the sum.
I need to come up with a yes or no signal in the 6th column by adding a positive and negative points
Col1 = A
Col2 = B
Col3 = C
Col4 = A
Col5 = C
So if value = A, add a point
If value = B, subtract a point
If value = C, add a point
So if the sum is negative, the 6th cell will be red and if the sum is positive, the 6th cell will be green.
If color with this rule is too, difficult, replace with the letter N or Y.
Has anyone does this before?
I need to come up with a yes or no signal in the 6th column by adding a positive and negative points
Col1 = A
Col2 = B
Col3 = C
Col4 = A
Col5 = C
So if value = A, add a point
If value = B, subtract a point
If value = C, add a point
So if the sum is negative, the 6th cell will be red and if the sum is positive, the 6th cell will be green.
If color with this rule is too, difficult, replace with the letter N or Y.
Has anyone does this before?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So if value = A, add a pointis that possible to have values other than A, B and C?
If value = B, subtract a point
If value = C, add a point
Yes. Substitute in the whatever values you want for A, B, and C.
An alternate way to write the formula so you can change the values easily is to reference a cell that has the value you want to match rather than putting that value in the formula. Here's the same formula, still in cell F5, rewritten this way, with A1=A, A2=B, and A3=C.
=IF(COUNTIF(A5:E5,$A$1)-CO
Notice the $ signs in front of each part of the reference. These will keep the reference as "absolute" when you copy the formula to other cells. If you copy this formula to F6 notice what happens:
=IF(COUNTIF(A6:E6,$A$1)-CO
The references for the rows that you're evaluating will change (from row 5 to row 6), but the value you're evaluating against, A1, A2, and A3, didn't change. This will make maintaining the spreadsheet easier if it grows over time.
An alternate way to write the formula so you can change the values easily is to reference a cell that has the value you want to match rather than putting that value in the formula. Here's the same formula, still in cell F5, rewritten this way, with A1=A, A2=B, and A3=C.
=IF(COUNTIF(A5:E5,$A$1)-CO
Notice the $ signs in front of each part of the reference. These will keep the reference as "absolute" when you copy the formula to other cells. If you copy this formula to F6 notice what happens:
=IF(COUNTIF(A6:E6,$A$1)-CO
The references for the rows that you're evaluating will change (from row 5 to row 6), but the value you're evaluating against, A1, A2, and A3, didn't change. This will make maintaining the spreadsheet easier if it grows over time.
ASKER
I went to try the first solution in it's simplest form
=IF(OR(A1="A"; A1="C"); 1; -1)
but excel gives an error with the formula
what could be missing here?
=IF(OR(A1="A"; A1="C"); 1; -1)
but excel gives an error with the formula
what could be missing here?
Try changing the ";" to ","
upload your excel file here if necesary
ASKER
I couldn't get the first solution to work with the second condition.
I'm investigated nested IF statements.
Here's a good website that explains them.
https://tinyurl.com/y8cp7955
I'm investigated nested IF statements.
Here's a good website that explains them.
https://tinyurl.com/y8cp7955
ASKER
This is the closest solution that I was able to get working.
Open in new window
As for color, a simple conditional formatting will do the trick.