# DIV and VALUE error on IF ABS statement

#### Helloitsme

##### New Member
I'm having a couple issues with formulas which i'm not smart enough to solve, I hope someone will be able to help me, I've added the minisheet at the end of this wall of text
K to N is filled manually.

Error:
Q displays error "VALUE" if K or M is N/A, Q should remain blank in this case

Explanation
O is the average of K and M with round 0, formula =IF(COUNTA(K4;M4)=2;ROUND(AVERAGE(K4;M4);0);"")
Q is the difference between K and M with ABS, IF=0 leave blank, formula IF(ABS([K]-[M])=0;"";ABS([K]-[M]))

---------

Error:
P displays error DIV/0 if L and N is blank, P should remain blank in this case
R displays error VALUE if L or N is N/A, R should remain blank in this case

Explanation
P is the average of L and N with round 1 decimal, add /10 to the end, formula =ROUND(AVERAGE(L4;N4);1)&"/10"
R is the difference between L and N with ABS, IF=0 leave blank, formula IF(ABS([L]-[N])=0;"";ABS([L]-[N]))

Hope everyone has a great weekend!

Pricebook14JAN22.xlsx
KLMNOPQR
1CGRCGTCMCRCMCTARATSRDTD
2 #DIV/0!
3N/AN/A614.8614,8/10#VALUE!#VALUE!
46171403.51015,3/10793.5
5577475.2526,1/10101.8
Listings
Cell Formulas
RangeFormula
O2:O5O2=IF(COUNTA(K2,M2)=2,ROUND(AVERAGE(K2,M2),0),"")
P2:P5P2=ROUND(AVERAGE(L2,N2),1)&"/10"
Q2:R5Q2=IF(ABS(K2-M2)=0,"",ABS(K2-M2))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R1:R89Cell Valuebetween 4,1 and 10textNO
R1:R89Cell Valuebetween 2,1 and 4textNO
R1:R89Cell Valuebetween 0 and 2textNO
Q1:Q89Cell Valuebetween 51 and 500textNO
Q1:Q89Cell Valuebetween 21 and 50textNO
Q1:Q89Cell Valuebetween 0 and 20textNO

#### Attachments

• Screenshot 2022-01-14 at 18.16.49.png
123.4 KB · Views: 5

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
I worked it out. DIV error could be fixed with IFERROR statement: =IFERROR(ROUND(AVERAGE(M2;O2);1)&"/10";"")
Value Error fixed with: =IF(OR(L3="";N3="");"";IFERROR(ABS(L3-N3);""))

.. or you could have continued with the 'COUNT' idea used in column O, but COUNT, not COUNTA. The advantage of this is that it would not hide other errors that you might actually want to know about.
Examples:

In P2: =IF(COUNT(L2;N2);ROUND(AVERAGE(L2;N2),1)&"/10";"")
In Q3: =IF(COUNT(K3;M3)=2;IF(ABS(K3-M3)=0;"";ABS(K3-M3));"")

.. or you could have continued with the 'COUNT' idea used in column O, but COUNT, not COUNTA. The advantage of this is that it would not hide other errors that you might actually want to know about.
Examples:

In P2: =IF(COUNT(L2;N2);ROUND(AVERAGE(L2;N2),1)&"/10";"")
In Q3: =IF(COUNT(K3;M3)=2;IF(ABS(K3-M3)=0;"";ABS(K3-M3));"")
This was exactly the solutions I looked for, and no extra errors occoured after I implemented these two so I'm very content! Thank you Peter

Replies
9
Views
676
Replies
3
Views
103
Replies
9
Views
266
Replies
4
Views
345
Replies
8
Views
190

1,203,096
Messages
6,053,510
Members
444,669
Latest member
Renarian

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back