Using Cells with number ranges for calculation

[Mack23]

Hello,

I have a pivot table that has ranges of number of units. I would like to use this field for looking up a value in that range to return the average number of minutes for that unit range.

So I am going to have another column that will have values to get summed ie 50+50+100+250=450. I then would like to use that 450 value to lookup in the units column of the above table, find the cell, and then return the average minutes for that unit range. I know there are probably different ways of doing this I just wanted to avoid making two columns for the unit range and just use the pivot table ranges.

Look forward to the creative solutions,
Mack

 

[Gerald Higgins]

So if the value is 450, the result should be 15.98, yes ?

If you are able to change your UNITS column, so that where you now have
100-299
300-499
500-699
and so on...

you have instead simply
100
300
500
and so on...

this can be achieved by a simple vlookup, such as
=vlookup(450,a1:b20,2)
assuming your table is in the range a1:b20.
Replace "450" with a cell reference, if that is the result of a formula.

 

[JamesCanale]

MrExcelPlayground4.xlsx
	H	I	J	K	L
2	TABLE			Thing	Lookup
3	100-299	10.1		550	20.3
4	300-499	15.5
5	500-699	20.3
6	700-899	22.4
Sheet15

Cell Formulas
Range	Formula
L3	L3	=XLOOKUP(K3,VALUE(LEFT(H3:H6,SEARCH("-",H3:H6)-1)),I3:I6,"",-1)