# 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! #### 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," 1,081,802
Messages
5,361,388
Members
400,628
Latest member
ganeshkhatri

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...