Formula to calculate % coming up as #DIV/0! - what am I doing wrong?

[halesowenmum]

Hi, I have the following spreadsheet:

In cell Q6 the value 237 (a total)
In cell P6 the value 99 (those out of the 237 that have been completed)
In R6 the result of the following formula on those two cells:

=(Q6-P6)/P6

The cell is formatted as %.

But I get #DIV/0! as the result - what have I done wrong?

I want to find out how much of 237 99 is, as a % - I always seem to find %s a real pain if you don't get 'em just right so any help would be so gratefully received.

Thanks very much indeedy.

 

[Flyboy65]

Hi,

Could you not use =IFERROR(P6/Q6,"") in Q6 to avoid the #DIV/0?

 

[hiker95]

halesowenmum,

How about:

Excel 2007
	P	Q	R
6	99	139	71.22%
7		139	0.00%
8	99
9
10
Sheet1

Cell Formulas
Range	Formula
R6	=IF(ISERROR(P6/Q6),"",P6/Q6)
R7	=IF(ISERROR(P7/Q7),"",P7/Q7)
R8	=IF(ISERROR(P8/Q8),"",P8/Q8)
R9	=IF(ISERROR(P9/Q9),"",P9/Q9)

 

[halesowenmum]

That will do very very nicely thank you. I shall pop that into my spreadsheet tomorrow and I'm sure that will do the trick for me. Thanks so much for taking the time to reply in such detail.

halesowenmum,

How about:

Excel 2007

	P	Q	R

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]139[/TD]
[TD="align: right"]71.22%[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]139[/TD]
[TD="align: right"]0.00%[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]R6[/TH]
[TD="align: left"]=IF(ISERROR(P6/Q6),"",P6/Q6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]R7[/TH]
[TD="align: left"]=IF(ISERROR(P7/Q7),"",P7/Q7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]R8[/TH]
[TD="align: left"]=IF(ISERROR(P8/Q8),"",P8/Q8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]R9[/TH]
[TD="align: left"]=IF(ISERROR(P9/Q9),"",P9/Q9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

halesowenmum,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.

 

[halesowenmum]

Don't worry - I'll be back!!! Have put it into my summary sheet and it's working a total treat.

 

[hiker95]

halesowenmum,

Don't worry - I'll be back!!!

I look forward to the challenge.

Thanks for the feedback.

You are very welcome. Glad I could help.