# Formula Help #Value on ABS

#### zone709

##### Well-known Member
HI this works, but if I am returning 0 right I get #value . I would like to return nothing if there is nothing if I can

=ABS(IFERROR(VLOOKUP(B2,'Paychex (2)'!\$D\$2:\$Z\$300,5,0)+0,0)+ABS(IFERROR(VLOOKUP(B2,'Paychex (2)'!\$D\$2:\$Z\$300,7,0)+0,0)))*(S2)

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### TheRobRush

##### New Member
if its retuning #value its not returning 0

one of easiest fixes for #value is just start formula with If(cellwithdata="" or 0 or whatever is messn it up,"",then start your formula)

Last edited:

or IFERROR

Last edited:

Hi,

What's in S2 ?

#### zone709

##### Well-known Member
In some cells there is nothing in S2 then some have numbers. I am just getting the #value when there is nothing in S2

#### zone709

##### Well-known Member
ok i just did Iferror and i get zero which is a better start, but how do i retuen just blank. Nothing i dont want or need the 0?

=IFERROR(ABS(IFERROR(VLOOKUP(B2,'Paychex (2)'!\$D\$2:\$Z\$300,5,0)+0,0)+ABS(IFERROR(VLOOKUP(B2,'Paychex (2)'!\$D\$2:\$Z\$300,7,0)+0,0)))*(S2),0)

#### jtakw

##### Well-known Member
Just change the 0 ( zero ) to Blank ( "" )

=IFERROR(ABS(IFERROR(VLOOKUP(B2,'Paychex (2)'!\$D\$2:\$Z\$300,5,0)+0,0)+ABS(IFERROR(VLOOKUP(B2,'Paychex (2)'!\$D\$2:\$Z\$300,7,0)+0,0)))*(S2),"")

#### zone709

##### Well-known Member
Just change the 0 ( zero ) to Blank ( "" )

=IFERROR(ABS(IFERROR(VLOOKUP(B2,'Paychex (2)'!\$D\$2:\$Z\$300,5,0)+0,0)+ABS(IFERROR(VLOOKUP(B2,'Paychex (2)'!\$D\$2:\$Z\$300,7,0)+0,0)))*(S2),"")

Thanks for the help guys

#### zone709

##### Well-known Member
Hi back again had one more question. I added "" which works now, but is there anyway to also add "" and 0 because it picks up 0 if its zero and rather it just blank also?

Replies
3
Views
155
Replies
1
Views
105
Replies
15
Views
754
Replies
2
Views
116
Replies
5
Views
117

1,186,662
Messages
5,959,062
Members
438,396
Latest member
Louise Rodway

### 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