Formula Help for Averages

DAS

New Member
Joined
Jul 11, 2008
Messages
36
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! :biggrin:
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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
Joined
Jul 11, 2008
Messages
36
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
Joined
Mar 10, 2004
Messages
19,406
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
Joined
Jul 11, 2008
Messages
36
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," :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,087
Members
414,426
Latest member
fraru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top