Forum Discussion
Implementing an Excel formula as a calculated column/measure
Hello,
I need to add a custom column or measure that would replicate the follwing Excel 2010 formula. Please note that the formula compares values from current and previous rows:
Excel:
=IF($B3=A$2,-1,0)+IF($C3=A$2,1,0)+IF(ISNUMBER(A2),A2,0)
=IF($B4=A$2,-1,0)+IF($C4=A$2,1,0)+IF(ISNUMBER(A3),A3,0)
=IF($B5=A$2,-1,0)+IF($C5=A$2,1,0)+IF(ISNUMBER(A4),A4,0)
... etc
Thank you,
M.R.
No prob - no need to apologize.
You're missing 1 step: "ID" (between #"Added Index" and SumID)
Having trouble reading your screenshots, but have the feeling that you first column-name (where I was expecting to see "H000018")is actually "ID". Then you would have the same problem like Sean and the code wouldn't work.
My solution would only work if you would skip the "ID" and take the column names like in row 2 of your Excel-screenshots.
12 Replies
What's in Row 1 - Column Names?
Yes, column names are in Row 1: [ID], [OLD_STATE], [NEW_STATE], [CALCULATION]. Column D that contains the formula
is [CALCULATION]
Then what is in Cells B2 and C2?
In columns B and C are numbers (integers)
On What Row goes the result of this =IF($B3=A$2,-1,0)+IF($C3=A$2,1,0)+IF(ISNUMBER(A2),A2,0) => in D2 or D3
In Row D2
Thank you.
What is in cell A2? Can you post a sample data set?
Column A B C D
Row 1 => ID OLD_STATE NEW_STATE CALCULATION
Row 2 => A2
