Hi All,

I have two formulas I am trying to achieve and require some help



Attached if a sample spreadsheet

Please help

Also can do a vlookup from a pivot table?

Attached Files

sample excel help required.xlsx (9.5 KB, 9 views)

The average is given by (you can refer to a cell value instead of hard-coding the value)
Summing values in N Although you asked for a count the result you gave is the sum of the values.

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

As for your last question you could either use the GETPIVOTDATA function ( not very flexible) or have a look at this article.
Using the free POWERPIVOT add-in also has it's advantages, but I can't help you much with that one.

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thank you

Sum if worked perfectly

The average if, seems to work for only one argument, attached this is the actual workbook. Words for 6 but not 7?

Attached Files

sample excel help required2.xlsx (9.0 KB, 12 views)

This has to do with the way Excel treats numbers.
If you format your result with 10 or more decimals, it is not 7 but 6,99999...
As you formatted with 0 decimals what you see is 7, but XL sees 6,9999
The workaround is to wrap your formula with the ROUND function ( with 0 decimals). Then all should be OK.
Like A little shorter is

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post