Stringing formulas again

[DavidB]

Hey there Roger, once again this formula thing is driving me crazy...I can
visualize what I want to do but can't work out the formula...This is how I
see it in my mind
=IF(AI13,AW13,BI13,BW13,CJ13="N","",IF(AI13,AW13,BI13,BW13,CJ13="R","",SUM(AI13+AW13+BI13+BW13+CJ13/100))))

What do you think?
Cheers
David

 

[CLR]

Perhaps this will help...........

=IF(OR(AI13="N",AW13="N",BI13="N",BW13="N",CJ13="N",AI13="R",AW13="R",BI13="R",BW13="R",CJ13="R"),"",SUM(AI13,AW13,BI13,BW13,CJ13/100))

Note that formula should be all in one cell......watch out for email
wordwrap...

Vaya con Dios,
Chuck, CABGx3

 

[DavidB]

Thank you for the tip. At least now the #Value thing has stopped. However,
it's the SUM part that is now a problem. I am getting a crazy output in L13
(where the result needs to show) so when I entered 89 into AI13, 90 into
AW13, 78 into BI13, 71 into BW13 and 82 into CJ13. Then L13 displayed 8200%
instead of an average score out of 100% for all five...am I explaining it
correctly??

Cheers
David

 

[Roger Govier]

Hi David

When I posted the Average formula, I said you would need to divide the
whole result by 100 before formatting as a percentage. If you do that,
your cell will correctly show 82% and not 8200%

=AVERAGE(AI13,AW13,BI13,BW13,CJ13)/100

 

[DavidB]

Yes your absolutely right...told you it would take awhile to digest what you
said )

Cheers
David