Returning different values for a similar error (Dividing by 0)

[Paprika]

Hello! I'm very new to Excel.

I have a spreadsheet where oftentimes my denominator is 0 (it's frustrating, but that's how my work wants it done).

Basically, if the number is 0/0 I want it to come up as "-". If the number is (>0)/0, I want it to return 100%.

I know that the error is technically the same error, so I can get it to return 100% or -, but not an either/or type situation. Is this possible? Hope I made sense.

 

[AnAnalyst]

Hi, how about something like:

Sheet1

*	A	B
*
*
*

<tbody>
[TD="bgcolor: #CACACA, align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]
[TD="align: center"][/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"]100%
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]
[TD="align: center"][/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"]10%
[/TD]

[TD="bgcolor: #CACACA, align: center"]7
[/TD]
[TD="align: center"]200
[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =IF(AND(A2=0,A3=0),"-",IF(AND(A2<>0,A3=0),1,A2/A3)) B4 =IF(AND(A4=0,A5=0),"-",IF(AND(A4<>0,A5=0),1,A4/A5)) B6 =IF(AND(A6=0,A7=0),"-",IF(AND(A6<>0,A7=0),1,A6/A7)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[T. Valko]

Hello! I'm very new to Excel.

I have a spreadsheet where oftentimes my denominator is 0 (it's frustrating, but that's how my work wants it done).

Basically, if the number is 0/0 I want it to come up as "-". If the number is (>0)/0, I want it to return 100%.

I know that the error is technically the same error, so I can get it to return 100% or -, but not an either/or type situation. Is this possible? Hope I made sense.

What version of Excel are you using?

 

[Aladin Akyurek]

Hello! I'm very new to Excel.

I have a spreadsheet where oftentimes my denominator is 0 (it's frustrating, but that's how my work wants it done).

Basically, if the number is 0/0 I want it to come up as "-". If the number is (>0)/0, I want it to return 100%.

I know that the error is technically the same error, so I can get it to return 100% or -, but not an either/or type situation. Is this possible? Hope I made sense.

Either...

=IF(B1,A1/B1,IF(A1,1,"-"))

Or...

=IF(B1,A1/B1,IF(A1,1,0))

which can be custom-formatted as:

[=0]"-";0%

 

[Paprika]

Excel 2010.

 

[T. Valko]

What version of Excel are you using?

Excel 2010.

One way...

=IF(AND(A1>0,B1=0),1,IFERROR(A1/B1,"-"))

 

[Rick Rothstein]

Maybe this way (works for any version of Excel)(assuming Columns A and B contain your values)...

=IF(OR(A2="",B2=""),"",IF(B2<>0,A2/B2,IF(A2=0,"-",1)))

Note that this formula can be copied down past existing data in anticipation of future data... it display the empty string ("") if either cell does not contain a number yet.