Unsure why getting #VALUE error in a AVERAGE(IF(SUBTOTAL... formula

[ashleighbrown]

Hi,

I'm trying to write a formula that calculates the average of a score (a decimal value) depending upon what level the assessment was (1-6), so there is an average score for each assessment level. However I'd like for the formula to display the average score depending upon the visible data so if the sheet is filtered by a class it'll update to show that classes average and not the overall. However the formula is pulling a '#VALUE!' error and I can't work out why as I'm not super familiar with some of the functions like ROW and OFFSET I'm having to use.

The formula as it stands is:
=AVERAGE(IF(SUBTOTAL(2,OFFSET(S55,ROW(S55:S60)-ROW(S55),0)),IF(R55:R60="1",S55:S60)))

Where the data it is looking at is
:

(This isn't the actual data but I was practicing on some random values that are structured the same to see if I could work out what the issue is, so its definitely a typed value with no spaces so I don't think that is the issue)

If anyone has any ideas it would be super helpful!

Thank you

 

[Fluff]

Try confirming the formula with Ctrl Shift Enter, rather than just enter.

 

[ashleighbrown]

Try confirming the formula with Ctrl Shift Enter, rather than just enter.

Just tried this now - this changes it to a #DIV/0! error but unsure why as to me there is no division by 0 in the formula..

 

[Fluff]

You also need to remove the quotes from the "1"

 

[ashleighbrown]

You also need to remove the quotes from the "1"

Ohhh this has worked thank you so much! I'm so used to having references to strings of text which I always write in quotations rather than numbers I didn't realise the "" weren't necessary if it was a pure numeric value - thank you!!

 

[Fluff]

You're welcome & thanks for the feedback.