Formula Help #Value on ABS

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,040
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 5, 2018
Messages
39
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:
Upvote 0

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,040
Office Version
  1. 365
Platform
  1. Windows
In some cells there is nothing in S2 then some have numbers. I am just getting the #value when there is nothing in S2
 
Upvote 0

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,040
Office Version
  1. 365
Platform
  1. Windows
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)
 
Upvote 0

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
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),"")
 
Upvote 0

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,040
Office Version
  1. 365
Platform
  1. Windows
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
 
Upvote 0

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,040
Office Version
  1. 365
Platform
  1. Windows
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?
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top