Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-10-2017, 07:15 PM
misokol misokol is offline Windows 10 Office 2013
Novice
 
Join Date: Jan 2017
Location: Spring TX
Posts: 2
misokol is on a distinguished road
Default Problem with Value function


I have a column of data that contains numbers, empty cells, and cells that are empty strings set by an if statement such that the value is specified as "". If this cell is an argument to VALUE() the number is converted, the empty cell becomes a 0, but the string input results in an error. The NUMBERVALUE() works however and converts the empty string to a 0. Thus my formulae work okay since it sees a number. When I try to run this S/S in earlier versions of Excel where NUMBERVALUE is not available, it cannot find a workaround. Is there a function that will convert an empty cell to a 0. I could do multiple IFs and test for empty cell and set the value to a numerical 0, but was looking for simpler implementation.
Reply With Quote
  #2  
Old 01-10-2017, 11:06 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 772
xor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the rough
Default

What about

=--ISNUMBER(A1)
Reply With Quote
  #3  
Old 01-11-2017, 08:53 AM
misokol misokol is offline Windows 10 Office 2013
Novice
 
Join Date: Jan 2017
Location: Spring TX
Posts: 2
misokol is on a distinguished road
Default

The column was left blank to indicate a quantity was not defined in the budget. This is different than saying it was defined at zero. When I calculate a deviation from this value, VALUE or NUMBERVALUE treats it as zero. But when I used a formula to set the cell value I use "" since it shows as a blank. Is there a way to set a cell value to BLANK rather than "", and then I could change my formula.
Reply With Quote
  #4  
Old 01-11-2017, 08:59 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 772
xor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the rough
Default

Can you upload a file (click Go Advanced) which shows what you have and what you want?
Reply With Quote
Reply

Tags
numbervalue, value_function
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with function PRA007 Word VBA 2 01-13-2016 01:11 AM
Word 2007 Review Function Problem sbudaj Word 0 10-09-2015 12:30 PM
Function problem li roy Excel 2 02-18-2012 02:44 AM
Checkmark function problem todddesignr PowerPoint 0 07-29-2011 07:40 AM
Search Function Problem MageWind Excel 2 06-29-2011 09:46 AM


All times are GMT -7. The time now is 07:32 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft