Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 27th 06, 10:30 PM posted to microsoft.public.excel.newusers
edrachel
 
Posts: n/a
Default want to return "n/a" or "0"


Hi, I am trying to do a formula that will either return a percentage or
if either of two cells are blank, then I want it to return either a "0"
or "n/a". Here is my formula =(J5/H5)-100% (this gives me the
recipacal of the percentage). It works fine. Now what do I add to get
it to return either "0" or "n/a" if either J5 or H5 is blank or both are
blank? Hope this makes sense. Background - I am trying to divide one
number by another to get the percentage of the difference 8/10-100%=20%
but if there are no numbers in the cells and nothing to divide, then I
want it to return "0" or "n/a".

Thanks.


--
edrachel
------------------------------------------------------------------------
edrachel's Profile: http://www.excelforum.com/member.php...fo&userid=4736
View this thread: http://www.excelforum.com/showthread...hreadid=505888


  #2   Report Post  
Old January 27th 06, 11:12 PM posted to microsoft.public.excel.newusers
goober
 
Posts: n/a
Default want to return "n/a" or "0"


=IF(OR(H5="",J5=""),"N/A",(J5/H5)-100%)

Hope it helps


--
goober
------------------------------------------------------------------------
goober's Profile: http://www.excelforum.com/member.php...o&userid=19838
View this thread: http://www.excelforum.com/showthread...hreadid=505888

  #3   Report Post  
Old January 27th 06, 11:14 PM posted to microsoft.public.excel.newusers
George Gee
 
Posts: n/a
Default want to return "n/a" or "0"

One way:

=IF(OR(J5="",H5=""),"n/a",(J5/H5)-100%)

Hope this helps.

George Gee


"edrachel" wrote in
message ...

Hi, I am trying to do a formula that will either return a percentage or
if either of two cells are blank, then I want it to return either a "0"
or "n/a". Here is my formula =(J5/H5)-100% (this gives me the
recipacal of the percentage). It works fine. Now what do I add to get
it to return either "0" or "n/a" if either J5 or H5 is blank or both are
blank? Hope this makes sense. Background - I am trying to divide one
number by another to get the percentage of the difference 8/10-100%=20%
but if there are no numbers in the cells and nothing to divide, then I
want it to return "0" or "n/a".

Thanks.


--
edrachel
------------------------------------------------------------------------
edrachel's Profile:
http://www.excelforum.com/member.php...fo&userid=4736
View this thread: http://www.excelforum.com/showthread...hreadid=505888





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return SEARCHED Column Number of Numeric Label and Value Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 07:16 PM
Count Intervals of 1 Numeric value in a Row and Return Count down Column Sam via OfficeKB.com Excel Worksheet Functions 8 October 4th 05 04:37 PM
Huge problem with "if" formula's [email protected] Excel Discussion (Misc queries) 16 July 20th 05 07:20 PM
How do create a formula to evalute a # to return 1 of 4 conditions Larry Excel Worksheet Functions 4 May 29th 05 12:58 AM
if the value of a cell in a range is not blank, then return the v. kvail Excel Worksheet Functions 2 April 8th 05 10:07 PM


All times are GMT +1. The time now is 07:00 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017