Hi All,
Trying to count if only when there is a value present on the SP page. I'm trying to show the average of the numbers (will weight afterwards) onto the Summary page for each qtr and month.
This is what I have now but can't seem to figure out why it doesn't work.
=IF(COUNT(SP!C3:E5),AVERAGE(SP!C3:E5),"")
Thanks for your help.
try this array formula..
confirm with Ctrl+Shift+Enter and not just Enter
=AVERAGE(IF(ISERROR(SP!C3:E3),FALSE,IF(SP!C3:E3="",FALSE,SP!C3:E3)))
Adjust ranges accordingly
Life's a spreadsheet, Excel!
Say thanks, Click *
Try this ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
Does that helP?J5: =AVERAGE(IF(ISNUMBER(SP!C3:E3),SP!C3:E3))
That is close but I need it to return a blank or not an error if the support page (SP) does not have a value or is an error.
Try this..
or edited version of Ron's formula=IF(ISERROR(AVERAGE(IF(ISERROR(SP!C3:E3),FALSE,IF(SP!C3:E3="",FALSE,SP!C3:E3)))),"",AVERAGE(IF(ISERROR(SP!C3:E3),FALSE,IF(SP!C3:E3="",FALSE,SP!C3:E3))))
=IF(ISERROR(AVERAGE(IF(ISNUMBER(SP!C3:E3),SP!C3:E3))),"",AVERAGE(IF(ISNUMBER(SP!C3:E3),SP!C3:E3)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks