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:
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,005
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,005
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:
 

Forum statistics

Threads
1,081,802
Messages
5,361,388
Members
400,628
Latest member
ganeshkhatri

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top