I have a raw data here.
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Name | Score | Complexity | Points | Total with Points | Total without Points |
| Tom | 5 | 3 | 0.25 | 105.00% | 100.00% |
| Brenda | 5 | 4 | 0.5 | 110.00% | 100.00% |
| Mark | 5 | #VALUE! | 100.00% |
| L | M |
|---|---|
| Complexity | Points |
| 5 | 1 |
| 4 | 0.5 |
| 3 | 0.25 |
| 2 | 0.15 |
| 1 | 0.05 |
Column B = Score - Dynamic coming from users
Column C = Complexity (from users) and Column D = Points have fixed table. In the image below, user puts blank/no Complexity.
Image 1
Column D = Points
Formula:
=IFERROR(VLOOKUP(C2,L1:M6,2,FALSE),"-")
Column E: Total with Points is based on sum of Score (B) and Points (D) divided by 5 (highest complexity of table). The column's format is in Percentage.
Formula:
=(B2+D2)/5
Column F: Total without Points is based on Score (B) divided by 5 (highest complexity). The column's format is in Percentage.
Formula:
=B2/5
All formulas above are applied to each respective columns. Now, I am getting 100.00% for Cell F4 because of the formula applied for Column F. See Image 1.
How to put formula for this column that will also show an error value if its left cell has an error value but will still give the result for all other cells with values using (=B2/5).
Desired:
Image 2
#VALUE!errors showing or is the real requirement not to display values in columnsEandFunless bothBandCcontain valid entries?#VALUE!because I want all cells filled so I can get the average of the column by putting this formula in a cell let's say cell G1:=AVERAGE(IF(ISERROR(F2:F70),"",F2:F70))