Help with analysing data and calculating a percentage

[MzP]

Hello I need to add a 4th column to this table with a formula that works out the percentage of N.V.s against Vs in theory it should be simple and I thought I could just use divide the cells, however if both boxes state "0" I need it to state "0" rather than an error and if there are no N.V.s at all again "0" and finally if there are no Vs but at least 1 NV it needs to record 100% if that makes sense. (I'll post an example underneath)


Staff member | V | N.V. |
Example | 3 | 2 |
Example | 1 | 0 |
Example | 1 | 0 |
Example | 0 | 1 |
Example | 0 | 0 |
Example | 0 | 0 |
Example | 1 | 0 |

The answers should be as follows:

66%
0
0
100%
0
0
0

Thanks in advance. I know this is probably going to involve the IF function with AND? but I couldn't fully get my head round it

[tigertiger]

- Assuming your data in A:C columns, D:D will be result-column
- at D2 enter the formula

Please Login or Register  to view this content.

- copy down to all result-cells in column D

[MzP]

Thanks! That has almost nailed it but it gives me:

0.666666667 - Correct
0 - Correct
0 - Correct
1 - Correct
1 - ?
1 - ?
0 - Correct

When both columns are showing 0 it is still giving me a 1 when it should be 0 any ideas?

[tigertiger]

try edit the formula to the alternative

Please Login or Register  to view this content.

[MzP]

Bingo! You're a legend thanks!

[tigertiger]

Please, Mark SOLVED for this topic by edit your first post and click mark solved