# Formula Help for Averages

#### DAS

##### New Member
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! ### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Domenic

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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," Replies
7
Views
80
Replies
3
Views
105
Replies
7
Views
340
Replies
4
Views
192
Replies
7
Views
134