Cell format changes depending on formula.

[Russ Fuquay]

Hi,

I'm Russ, new to the forum. Looked for this in FAQ but didn't find the answer.

My formula works fine, but I need the first number of the answer displayed with only two decimal places. The formula averages a set of numbers, then adds " of " and a cell reference.

I want the answer displayed as 2.43 of 3. Here is the formula.

=IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123))&" of "& Z9,"") it works perfectly but displays 2.42857142857143 of 3.

The cell is formatted to NUMBER with two decimal places. . Changing the format has no effect, but changing the formula does.

If I use =IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123)) or =AVERAGE(W12:W123) the result is 2.43. When I add the next portion, &" of "& Z9,"") it changes to 2.42857142857143.

I've tried reformatting cells and nothing changes. This is the only cell I'm having problems with. The worksheet seems to be working fine.

I'm greatfull for any advice you can offer.

Thanks,

Russ

[Alf]

What happends if you change your formula to

Please Login or Register  to view this content.

Alf

[Pepe Le Mokko]

=IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123))&" of "& Z9,"") returns a text string as you have used concatenation and you cannot round it this way

Try

Please Login or Register  to view this content.

[newdoverman]

Seeing that the answer is going to be a text value this might help.

Please Login or Register  to view this content.

This means that if the average of the range W12:W123 is greater than 0, take the value of the average of W12 to w123, treat it as text with the format of "000.00" space of space the value in Z9.

[Russ Fuquay]

thanks Alf. This returned a Value error.

Russ

[Russ Fuquay]

Thanks newdoverman. This worked perfectly! Thank you so much. I really appreciate it.

russ

[Russ Fuquay]

Thanks Pepe Le Mokko,

This worked perfectly too! I appreciate your help very much.

Russ

[Pepe Le Mokko]

Wrong thread for this post

[Russ Fuquay]

How do I marked this thread as solved?
Russ

[newdoverman]

You're welcome. Glad to help. Thanks for the feedback.

[Pepe Le Mokko]

How do I marked this thread as solved?
Russ

I think it's in the thread tools, just above your OP