formula needed!

[thatEODguy]

look for a formula for:

Value of each cell in column C is either 0, 1, 2, 3, 4 or 5 depending on the vale of the column to it's left

0 = <20
1 = 20-40
2 = 41-82
3 = 83-144
4 = 145-207
5 = >207

 

[joeu2004]

In C1:

=MATCH(B1,{0,20,41,83,145,207.000000000001})-1

Assumes B1 >= 0. If not, change zero to something less than or equal to the minimum value of B1. -1E300 would do it "for sure" (pretty much).

PS.... Technically, =207+"5E-13" is greater than 207, but the formula above returns 4 instead of 5. My guess: you don't care. If you meant ">=208" instead of ">207", change 207.000000000001 to 208.

 

[thatEODguy]

Hey Joe, thanks for the quick response!

However, I have 272 columns to fill so i was wondering if there was a blanket formula to put in each cell without lets say for example "b1" in the formula but instead something that "looks to the cell to the left"

 

[thatEODguy]

IE instead of the lookup value being "B1", is there a way to have a lookup value of being "left of cell" or along those lines

 

[Aryatect]

Hi, Formula given by joeu2004 is pointing to left cell, once you paste this in C1 in excel and then say copy it and paste in K1 it will point to J1.

 

[joeu2004]

However, I have 272 columns

Can't fault me for reading your original question exactly as you wrote it. Maybe you can explain __fully__ what the problem is.

Why doesn't the reference to B1 work, assuming you copy that formula into all the cells?

For example, do you want the "to the left" reference to work even if you insert a new column to the left of column C?

You could replace B1 with OFFSET(C1,0,-1), when the formula is in C1. That refers to the cell "to the left", even if you insert a new column to the left of column C. (It does __not__ cause a circular reference.)

But I avoid that approach because OFFSET is a "volatile" function: it causes that formula in C1 and any formula that references C1 directly or indirectly to be recalculated whenever Excel recalculates anything in the workbook.

-----

In any case, if you are putting the formula into potentially 1000s of cells ("272 columns", each with many rows), it would be better to put the match limits into a range.

For example, enter 0, 20, 41, 83, 145 and 207.000000000001 into X1:X6. Then the formula would be:

=MATCH(B1,$X$1:$X$6)-1

 

[thatEODguy]

Hi, Formula given by joeu2004 is pointing to left cell, once you paste this in C1 in excel and then say copy it and paste in K1 it will point to J1.

My lack of experience with Excel hindered me from realizing this. Thank you to both Joe and Arya!

Joe, your original formula worked! Thank you.