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.

 

[hiker95]

halesowenmum,

If I understand you correctly:

Excel 2007
	P	Q	R
6	99	237	139%
Sheet1

Cell Formulas
Range	Formula
P6	=99
Q6	=237
R6	=(Q6-P6)/P6

Or, maybe:

Excel 2007
	P	Q	R
6	99	237	139%
Sheet1

Cell Formulas
Range	Formula
R6	=(Q6-P6)/P6

 

[nuked]

I'm guessing you just made a typo, as your formula is correct.

 

[halesowenmum]

Now I will freely admit that I am a total dunce when it comes to maths! Without Excel I'd never do anything mathematical!

So, surely 99 isn't 139% of 237 - it can't be a +100% value if it's less than the total can it? Do you see what I mean? (Or maybe this horrific cold and cough I've got and the copious quantities of liquid cough mixture I'm drinking have rotted my brain, finally).

 

[Robert Mika]

Hi, I have the following spreadsheet:

I want to find out how much of 237 99 is, as a % - .

=P6/Q6

which is approximate 42%

 

[Olddogandgrump]

Hi

Your formula in R6 should read =P6/Q6 and then formatted as percentage. This gives 41.77% (rounded to two d.p.)

Try that.

 

[jai9]

Hi,

You will get #DIV/0! error in that formula only if P6 is 0.

Maybe you should check that.

And later use the formula provided by Robert and Olddogandgrump.

Jai

 

[halesowenmum]

So this is why I got the #DIV message in the first place as in several cases I have no values or zero values which gives that result.

So if I want to find out the % of the total (not how much the total is more than the lower figure) but if there's a zero value to not get the #DIV message?? What exactly do I need to enter?????

 

[jai9]

Hi,

I am not sure of this.

Maybe someone else can help with this issue.

Jai

 

[Olddogandgrump]

Hi, My formula of =P6/Q6 will tell you what percentage P6 is of Q6. You will always get a #DIV/0 error in the target cell of there is no value in Q6 as dividing by 0 is a mathematical no no. The only way to avaid it occuring is to only have the formula in rows that contain data in column Q. Conditional formatting to hide the #DIV/0 may work.