Formula Help for Averages

[DAS]

Hello,

I am need of some assistance with a formula to calcualte some averages. I have the following range 85, 90, 95 and 97 each are given a value 0.5, 1.0, 1.5 and 2 respectively. I am using this formula to arrive at a number depending on where the Avg number lands.

=SUM(HLOOKUP(FLOOR(B1,5),$D$2:$G$3,2)+HLOOKUP(CEILING(B1,5),$D$2:$G$3,2))/2


Now an example of what I need this formula to do is if my Avg is 91 the result I want back is 1.1 and instead I am getting 1.25

I am using HLOOKUP because the range and the values are in the following format.

A 85 90
B 1.0 1.5

Also if the number is less than 85 the result is 0 and if the number is greater than 97 it remains goes no higher than 2.

Thank you for any assistance that can be provided!

 

[Domenic]

Let's assume the following...

A2:D3 contains:

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>85</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>90</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>95</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>97</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>0.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>1.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>2</TD></TR></TBODY></TABLE>

Try...

=IF(F2<A2,0,IF(F2>=D2,2,IF(LOOKUP(F2,$A$2:$D$2)<>F2,LOOKUP(F2,$A$2:$D$2,$A$3:$D$3)+(INDEX($A$3:$D$3,MATCH(F2,$A$2:$D$2,1)+(LOOKUP(F2,$A$2:$D$2)<>F2))-LOOKUP(F2,$A$2:$D$2,$A$3:$D$3))*(F2-LOOKUP(F2,$A$2:$D$2))/(INDEX($A$2:$D$2,MATCH(F2,$A$2:$D$2,1)+(LOOKUP(F2,$A$2:$D$2)<>F2))-LOOKUP(F2,$A$2:$D$2)),LOOKUP(F2,$A$2:$D$2,$A$3:$D$3))))

...where F2 contains the average, such as 91.

 

[DAS]

Thank you Domenic you are the man. I did a simple tweak to it to get the averages less than 85 to equal to 0 and greater than 97 equal 2 but you did the heavy work.

Thanks!!!

 

[Domenic]

Thank you Domenic you are the man.

You're very welcome!

I did a simple tweak to it to get the averages less than 85 to equal to 0 and greater than 97 equal 2 but you did the heavy work.

Thanks!!!

I see that the formula I posted got cut off. I meant to post the following formula...

=IF(F2 < A2,0,IF(F2>=D2,2,IF(LOOKUP(F2,$A$2:$D$2)<>F2,LOOKUP(F2,$A$2:$D$2,$A$3:$D$3)+(INDEX($A$3:$D$3,MATCH(F2,$A$2:$D$2,1)+(LOOKUP(F2,$A$2:$D$2)<>F2))-LOOKUP(F2,$A$2:$D$2,$A$3:$D$3))*(F2-LOOKUP(F2,$A$2:$D$2))/(INDEX($A$2:$D$2,MATCH(F2,$A$2:$D$2,1)+(LOOKUP(F2,$A$2:$D$2)<>F2))-LOOKUP(F2,$A$2:$D$2)),LOOKUP(F2,$A$2:$D$2,$A$3:$D$3))))<a2,0,if(f2>

Hopefully it's either the same or similar to the one you came up with.</a2,0,if(f2>

 

[DAS]

You're very welcome!

I see that the formula I posted got cut off. I meant to post the following formula...

=IF(F2 < A2,0,IF(F2>=D2,2,IF(LOOKUP(F2,$A$2:$D$2)<>F2,LOOKUP(F2,$A$2:$D$2,$A$3:$D$3)+(INDEX($A$3:$D$3,MATCH(F2,$A$2:$D$2,1)+(LOOKUP(F2,$A$2:$D$2)<>F2))-LOOKUP(F2,$A$2:$D$2,$A$3:$D$3))*(F2-LOOKUP(F2,$A$2:$D$2))/(INDEX($A$2:$D$2,MATCH(F2,$A$2:$D$2,1)+(LOOKUP(F2,$A$2:$D$2)<>F2))-LOOKUP(F2,$A$2:$D$2)),LOOKUP(F2,$A$2:$D$2,$A$3:$D$3))))<A2,0,IF(F2>

Hopefully it's either the same or similar to the one you came up with.</A2,0,IF(F2>

Thanks that the part I added. "=IF(F2 < A2,0,IF(F2>=D2,2,"