decimal places

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi al
I have a series of data that works on averages,

The formula in C27 through C 54 is
=IF(B27="","",AVERAGE(B11:O11)

However when the cell calculates some cells it returns a whole number (which is correct = 54/3= 18
but when it caluculates a number like 47/3 = 15.66666

If I format all the cells I get to one decimal place it looks messy

How do I format cells so that if it returns a whole number if it is 54/3 = 18 and 47/3 to return 15.7

Thanks in advanc

Alb
 
Hi again

One way is to use the TEXT-function, but this
has some disadvantages.

Insert in e.g. C27

=IF(B27="","",IF(MOD(AVERAGE(B11:O11),1)=0,TEXT(AVERAGE(B11:O11),"#"),
TEXT(AVERAGE(B11:O11),"#.#")))

Right-adjust the cell.

In another cell you can do e.g.

=C27*5

and get the correct result,
but in a situation like

=IF(C27>G45,.....)

and assuming G45 is a number
you will have to do

=IF(VALUE(C27)>G45,....)

to get the correct result.
 
If you could live with the whole number being displayed with a decimal point
(18.), you could custom format your cells:

###.#
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi all
I have a series of data that works on averages,:

The formula in C27 through C 54 is :
=IF(B27="","",AVERAGE(B11:O11))

However when the cell calculates some cells it returns a whole number (which
is correct = 54/3= 18)
but when it caluculates a number like 47/3 = 15.66666.

If I format all the cells I get to one decimal place it looks messy.

How do I format cells so that if it returns a whole number if it is 54/3 =
18 and 47/3 to return 15.7?

Thanks in advance

Alby
 
Back
Top