Displaying label based difference between neighbouring cells in Excel / Google Spreadsheets

Question

I'm making a system in Excel / Google Spreadsheets where data is transformed from raw numbers into smaller, universal categories. The purpose of the system is to measure change from year to year in a dataset. I want to calculate the difference from one cell to the next in the row and splitt this changes in data into 20 different steps.

So I have a spreadsheet of raw data, like this:

And then I have a set of categories like this. The column on the right representing the increase or decrease in value from the previous year:

So I need a formula that can both:

Calculate the difference between the current cell and the cell to the left of it.
Then figure out what number to put in, based on the "Change steps" columns that I posted a picture of.

Currently I have the following formula:

    =IF(AND(Data!B12-Data!C12<Formula!S2),"1",
IF(AND((Data!B12-Data!C12>Formula!S2,Data!B12-Data!C12<Formula!S3),"2",
IF(AND(Data!B12-Data!C12>Formula!S3, Data!B12-Data!C12<Formula!S4),"3",
IF(AND(Data!B12-Data!C12>Formula!S4, Data!B12-Data!C12<Formula!S5),"4",
IF(AND(Data!B12-Data!C12>Formula!S5, Data!B12-Data!C12<Formula!S6),"5",
IF(AND(Data!B12-Data!C12>Formula!S6, Data!B12-Data!C12<Formula!S7),"6",
IF(AND(Data!B12-Data!C12>Formula!S7, Data!B12-Data!C12<Formula!S8),"7",
IF(AND(Data!B12-Data!C12>Formula!S8, Data!B12-Data!C12<Formula!S9),"8",
IF(AND(Data!B12-Data!C12>Formula!S9, Data!B12-Data!C12<Formula!S10),"9",
IF(AND(Data!B12-Data!C12>Formula!S10, Data!B12-Data!C12<Formula!S11),"10",
IF(AND(Data!B12-Data!C12>Formula!S11, Data!B12-Data!C12<Formula!S12),"11",
IF(AND(Data!B12-Data!C12>Formula!S12, Data!B12-Data!C12<Formula!S13),"12",
IF(AND(Data!B12-Data!C12>Formula!S13, Data!B12-Data!C12<Formula!S14),"13",
IF(AND(Data!B12-Data!C12>Formula!S14, Data!B12-Data!C12<Formula!S15),"14",
IF(AND(Data!B12-Data!C12>Formula!S15, Data!B12-Data!C12<Formula!S16),"15",
IF(AND(Data!B12-Data!C12>Formula!S16, Data!B12-Data!C12<Formula!S17),"16",
IF(AND(Data!B12-Data!C12>Formula!S17, Data!B12-Data!C12<Formula!S18),"17",
IF(AND(Data!B12-Data!C12>Formula!S18, Data!B12-Data!C12<Formula!S19),"18",
IF(AND(Data!B12-Data!C12>Formula!S19, Data!B12-Data!C12<Formula!S20),"19",
IF(AND(Data!B12-Data!C12>Formula!S20, Data!B12-Data!C12<Formula!S21),"20",
)))))))))))))))))))))

I thought this would work, but all I get is #ERROR!. Any tips?

I also tried this with =IF(AND(ABS(Data!F25-Data!G25)Formula!S3), "2")) But still get an error — Kaosmos, Mar 04 '17 at 16:41

score 0 · Answer 1 · answered Mar 04 '17 at 18:35

If your Change steps: (#1, #2, #3 etc.) are in column R of the Formula sheetthe following INDEX/MATCH function should do the trick:

=IF(Data!B12-Data!C12<Formula!$S$2,1,SUBSTITUTE(INDEX(Formula!$R$2:$R$21,MATCH(Data!B12-Data!C12,Formula!$S$2:$S$21,1)),"#","")+1)

Setup in formula sheet:

Result:

score 0 · Accepted Answer · answered Apr 11 '17 at 13:31

I figured it out myself. The correct formula:

IF(AND(OFFSET(INDIRECT("Data!"&address(row(),column())),0,-1)-ABS(INDIRECT("Data!"&address(row(),column())))>Formel!J3, OFFSET(INDIRECT("Data!"&address(row(),column())),0,-1)-ABS(INDIRECT("Data!"&address(row(),column())))<Formel!J4), "2",

And so on.

Displaying label based difference between neighbouring cells in Excel / Google Spreadsheets

2 Answers2