I have a spreadsheet where data from one tab is used to create a column graph on another
On the tab with the column graph I have a vlookup statement to pull through a completion % of training courses. This is set to return #N/A if the lookup value is 0% so the label does not appear on graphs. This look up is
=IF(VLOOKUP($B$4,TRAINING TAB!$A:$K,2,FALSE)=0,NA(),VLOOKUP($B$4,TRAINING TAB!$A:$K,2,FALSE))
This works and brings back the correct values. However, the second part of the graph is made up of the difference between course complete and 100% so was a basic formula. I have managed to get this to also include #N/A for zero values by using the formula
=IF(1-D9=0,NA(),1-D9) (Where D9 is the % of course complete).
My problem is that if the value in course complete is #N/A I need course not complete to show 100% e.g
an example of what I need is below:
% complete is the result of the vlookup and % not complete needs to be based on this.
For both columns if the answer is zero I need #N/A
Any help gratefully received!
<tbody>
</tbody>
On the tab with the column graph I have a vlookup statement to pull through a completion % of training courses. This is set to return #N/A if the lookup value is 0% so the label does not appear on graphs. This look up is
=IF(VLOOKUP($B$4,TRAINING TAB!$A:$K,2,FALSE)=0,NA(),VLOOKUP($B$4,TRAINING TAB!$A:$K,2,FALSE))
This works and brings back the correct values. However, the second part of the graph is made up of the difference between course complete and 100% so was a basic formula. I have managed to get this to also include #N/A for zero values by using the formula
=IF(1-D9=0,NA(),1-D9) (Where D9 is the % of course complete).
My problem is that if the value in course complete is #N/A I need course not complete to show 100% e.g
an example of what I need is below:
% complete is the result of the vlookup and % not complete needs to be based on this.
For both columns if the answer is zero I need #N/A
Any help gratefully received!
Course Name | % Complete | % Not Complete |
1 | 100 | #N/A |
2 | 75 | 25 |
3 | 25 | 75 |
4 | #N/A | 100 |
5 | 50 | 50 |
<tbody>
</tbody>