How to average using VLOOKUP

[conks]

Hi,

Can anybody help with the attached workbook please.

It will open on the averages tab.

I'm aiming to average data from the tender register tab in the select columns based upon the estimator etc.

I was thinking a VLOOKUP may help but not sure how this would tie in with an average function ?

Thanks.

[Paul]

Hi Conks, perhaps this formula in D3 on the Averages tab:

=AVERAGEIF('Tender Register'!F1:F15,C3,'Tender Register'!G1:G15)

This function is only available in Excel 2007+. If you need compatibility for older versions other formulas can be used.

[conks]

That's brilliant thanks.

My works laptop uses 2003 version though so I guess this won't compute.

Are there any other ways?

Thanks.

[arthurbr]

Then try

=AVERAGE(IF('Tender Register'!F1:F15=C3,'Tender Register'!G1:G15,0))

entered with Ctrl+Shift+Enter

(also indicate version of XL used if different of the version in your profile)

[conks]

Thanks - one last question.

I've tried to adapt the same function for the 'ave finishing position' column but it hasn't worked?

What is required to average out the finishing position etc. ?

Thanks.

[NBVC]

Should be similar formula, just exchanging the Tender Register'!G1:G15 for the appropriate range...I assume here, Tender Register'!K1:K15

Remember that for Arthurbr's solution you need to confirm the formula by holding the CTRL+SHIFT keys and pressing ENTER.. to get the { } brackets to appear around the formula.

[conks]

Another problem that's arised.

The 'finishing position' formula states to show '1' if there is no value in any of the cells in column I.

Whilst this works fine if I try to copy down (as in the example attached) the cells in finishing postion it shows a 1 as there is no value/data in column I.

Is there any way for the copy down for the '1' not to be shown?

Thanks.

[NBVC]

Try first changing the formula to:

=IF(I6=0,1,"") (i.e. no quotes around the 1).

Then you can apply conditional formatting

Cell Value is >> equal to >> =1 and format font to White (to match your background cell colour).

[conks]

Having completed all in 2007 naturally using my works laptop which runs 2004 none of teh formulas work (as indicated!).

I did try the CTRL+SHIFT+ENTER routine but it hasn't worked?

Can you please have a look at the latest v4.0 upload to see why - can't figure it out.

Many thanks.

[NBVC]

For the MAC computer the equivalent to CTRL+SHIFT+ENTER is COMMAND+RETURN

For PC 2007, MAC 2008 and later, you can use AVERAGEIFS as

i.e. in D12:

Please Login or Register  to view this content.

For the array formula, you have it correct in D3, but did not use the CSE key combo in the rest of the cells. After you confirm the D3 formula, just copy it down, the { } will follow..

Then repeat the confirmation in the E3 formula and copy it down.

[conks]

Oh, and to be really annoying I'm also looking to add a further column headed 'strike/success rate'.

The idea would be to create a formula (VLOOKUP?) to detect the Estimator in the Tender Registry sheet, calculate the total number of tenders handled by the Estimator then, total from the Finishing Position the total of number 1 finishes to provide and show a %.

So, for example, if Estimator MAB was entered in the tender registry sheet say 5 times, and out of the 5 entries (tenders) finshed first on 2 occasions the strike/success rate column would read 40%.

Could this be done using VLOOKUP?

Thanks.

[conks]

For the array formula, you have it correct in D3, but did not use the CSE key combo in the rest of the cells. After you confirm the D3 formula, just copy it down, the { } will follow..

Then repeat the confirmation in the E3 formula and copy it down.

The problem is that it's not returning the correct data - i.e MAB ave should read 247,500 and not 99 as shown?

[NBVC]

The last formula I posted above was a little wrong... sorry.

the formula should be using AVERAGEIF... not AVERAGEIFS

so:

=AVERAGEIF('Tender Register'!$F$1:$F$5000,C3,'Tender Register'!$G$1:$G$5000)

since you are only checking one condition.

The AVERAGEIFS is for multiple conditions.

and the pre-2007/2008 array formula would be:

=AVERAGE(IF('Tender Register'!$F$1:$F$5000=C3,'Tender Register'!$G$1:$G$5000))

confirmed with CTRL+SHIFT+ENTER or COMMAND+RETURN

[conks]

But with the array formula it still returns 99 when the answer should be 247,500 ?

[NBVC]

This array formula returns me 247,500?

=AVERAGE(IF('Tender Register'!$F$1:$F$5000=C3,'Tender Register'!$G$1:$G$5000))

[NBVC]

This array formula returns me 247,500?

=AVERAGE(IF('Tender Register'!$F$1:$F$5000=C3,'Tender Register'!$G$1:$G$5000))

[NBVC]

Oh, and to be really annoying I'm also looking to add a further column headed 'strike/success rate'.

The idea would be to create a formula (VLOOKUP?) to detect the Estimator in the Tender Registry sheet, calculate the total number of tenders handled by the Estimator then, total from the Finishing Position the total of number 1 finishes to provide and show a %.

So, for example, if Estimator MAB was entered in the tender registry sheet say 5 times, and out of the 5 entries (tenders) finshed first on 2 occasions the strike/success rate column would read 40%.

Could this be done using VLOOKUP?

Thanks.

Try this ARRAY formula:

Please Login or Register  to view this content.

or this non-ARRAY formula in 2007/2008:

Please Login or Register  to view this content.

[conks]

The COUNT array has worked brilliantly thanks.

But the problem remains re the average.

Please have a look at the v4.0 upload again, I'm sure it's something I've done?

Thanks.

[NBVC]

You are not confirming it with CSE keys....

Take a look.

I am also confused as to whether you are wanting a 2003 or 2007 solution.

Your column D formula is 2003 version and column E shows the 2007 version of the AVERAGE If conditional formulas

[conks]

Thanks very very much for your help.

Have finally concluded all and it works brilliantly well.

Regards.