CODEWITHSUNDEEP
excelexcel-formulaexcel-2010spreadsheetformula
user3470877
user3470877

Reputation: 1

Want formula to return a number value or blank

I have two columns (A & B) of information. I'm trying to create a third (C) with the answer of B divided by A in the form of a percentage or if 100% a blank or dash (-). Below is an example.

I've tried several different formula combinations like =IF((B38/A38)<100,(B38/A38)," ") I can get the percentage answers okay but have not been able to get the wanted result of a blank or a dash. 

**A**  **B**    **C**  
1.99    0.99    49.7%  
1.99    1.99      
3.99    3.99    -  
2.99    1.99    66.6%

Thank you for any help given.

Upvotes: 0

Views: 102

Answers (2)

Sahil Mittal
Sahil Mittal

Reputation: 20753

You are not getting the desired result for the blank because the statement is always true. replace 100 by 100%-

=IF((B38/A38)<100%,(B38/A38),"")

Upvotes: 1

Ron Smith
Ron Smith

Reputation: 3266

100% displayed as an integer is 1, so you want =IF(B1/A1=1,"-",B1/A1)

Upvotes: 1

Related Questions