excelformula

How to skip calculating a cell if its left cell has an error value

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 img1

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. Pls 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:

Image2 Img2

I appreciate your help.

Solution

  • Use IF() function with ISERROR(). Try-

    =IF(ISERROR(E2),"",B2/5)

    If you need error values as result then could try-

    =IF(ISERROR(E2),B2/E2,B2/5)