Link to home
Start Free TrialLog in
Avatar of jlcannon
jlcannon

asked on

Adding conditions to a formula in excel

I have a formula in a cell on a worksheet that is =SUM(D18:D46)/COUNT(D18:D46) and it give me a pecentage. Basically I am using this as a quality rating. What I would like to know how to do it make it to where cell reads incomplete until there is data in all the fields it looks at. I would like to have it, if the certain cell is not relevent for a particular area to be able to put N/A in the cell and have the formula ignor it but if the field is empty to treat it like a 0 so it will have an impact on the final percentage. Quality-RCI-Assessment-Tool-ES.xls
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image
=IF(COUNT(D18:D46)=0,"Incomplete",SUM(D18:D46)/COUNT(D18:D46))

Kevin
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image
I think that if you want to count blanks as zero then you can divide by the count of non N/A cells, try this version in D14 copied across

=IF(COUNTA(D18:D46),SUM(D18:D46)/COUNTIF(D18:D46,"<>N/A"),"")

regards, barry
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image
...my suggestion will give you 85% for column D because it's counting blanks as zeroes.....if you want "incomplete" rather than a blank then put that in place of "" in my suggestion, i.e.

=IF(COUNTA(D18:D46),SUM(D18:D46)/COUNTIF(D18:D46,"<>N/A"),"incomplete")

barry
Avatar of jlcannon
jlcannon

ASKER

@ barryhoudini when I use =IF(COUNTA(D18:D46),SUM(D18:D46)/COUNTIF(D18:D46,"<>N/A"),"incomplete") it returns "TRUE" in the box

@zorvek this still returns 100% for me. I am looking to count a blank cell as a 0 and not count an n/a.
Avatar of jlcannon
jlcannon

ASKER

sorry guys my last post is inaccurate. If there is any blank cells in the column I want it to retunr an incomplete so it forces then to either choose 1 or 0 or n/a and if its n/a i want it to ignore the cell and not have it factor into the %
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image
Try this:

=IF(COUNT(D18:D44)=0,"Incomplete",SUM(D18:D44)/(ROWS(D18:D44)-COUNTIF(D18:D44,"N/A")))

See attached.

Kevin
Quality-RCI-Assessment-Tool-ES.xls
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jlcannon
jlcannon

ASKER

@zorvek,  this is comming close but it is showing me 85% but since there are blanks it should say incomplete.
Avatar of jlcannon
jlcannon

ASKER

@zorvek the post directly after the one giving me 85% worked perfect. thank you.
Avatar of jlcannon
jlcannon

ASKER

Thank you. this was the exact solution I hoped to find.