vlookup

[itazmia]

Hello,

I am looking for a way to average all the numbers that begin with 1 in my lookup table where i have been given a number like 1.4. If i was given the number of 2.3 i would be looking to average all of the numbers starting with 2 in my lookup table. How would i do this?

Is it possible to create a range so that when i performed a lookup i could say i want the average of the numbers between 1.01 and 1.99

 

[Fluff]

Hi & welcome to MrExcel.
I have no idea where your data is, but try it like

=AVERAGEIFS(C:C,B:B,">="&INT(G6),B:B,"<"&INT(G6)+1)

 

[itazmia]

Hi & welcome to MrExcel.
I have no idea where your data is, but try it like

=AVERAGEIFS(C:C,B:B,">="&INT(G6),B:B,"<"&INT(G6)+1)

Hi, thanks for replying i have updated my question to show where the data is

 

[itazmia]

 

[Fluff]

Have you changed the ranges to suit your data?

 

[itazmia]

Have you changed the ranges to suit your data?

Hello, i am trying now but i keep getting a DIV/0 error

 

[itazmia]

Have you changed the ranges to suit your data?

my new formula is
=AVERAGEIFS(G7:G17,H7:H17,">"&INT(L9),H7:H17,"<"&INT(L9)+1)
but i keep getting an error

 

[Fluff]

You have the ranges the wrong way round, it should be

=AVERAGEIFS(H7:H17,G7:G17,">"&INT(L9),G7:G17,"<"&INT(L9)+1)

 

[itazmia]

You have the ranges the wrong way round, it should be

=AVERAGEIFS(H7:H17,G7:G17,">"&INT(L9),G7:G17,"<"&INT(L9)+1)

That is perfect that works. Thank you so much for your help

 

[Fluff]

You're welcome & thanks for the feedback.