Formula for differentiating between integers and numbers with decimal places

[Grumpy88]

Hi.


I have a column of numbers, some of which are whole numbers and others of which have one decimal place. Is there a formula that I can apply to the entire column that will differentiate between the two number types, and then work differently with each type (I am thinking formula-based conditional formatting here)?


Thanks.

 

[NARAYANK991]

Hi ,


You can use either of the following formulae :


=MOD(Number,1)=0


=INT(Number)=Number


Both of them will return TRUE for an integer , and FALSE otherwise.


Narayan

 

[Faseeh]

Or this one:


=VALUE(A1-INT(A1))<>0


...will highlight cells with decimal points.


Faseeh

 

[Grumpy88]

Thanks NARAYANK991. The second formula works a treat.


However, the conditional formatting part doesn't. Does conditional formatting offer the ability to add text (concatenate) to a number too, or can it only be used for cell format-type things such as colour and font?


Thanks.

 

[NARAYANK991]

Hi ,


What did you have in mind ? Can you give an example of what a cell should display if it contains an integer , and what it should display if it has a decimal number ?


The CF part can only change the cell attributes Number , Font , Border and Fill , I think.


If you want to add text to whatever number is present , it can probably be done , but it all depends on exactly what you want.


Narayan

 

[Grumpy88]

Okay, I'm clearly getting ahead of myself again!


My difficulty is that I want the result of this proposed formula to appear in a pivot table, which immediately restricts my options in what I can request (seeing as how a pivot table doesn't accept formulae).


Anyway, what I want is that if the number is an integer (obviously I am drawing the data here from a column in my source table), then just the integer should appear in the pivot table column (i.e. a "23" in my source table row label should appear as "23" in the corresponding pivot table row for that Row Label as well).


However, if the number in the source table's column is for example "23.1" (all those numbers with a decimal place are always "XX.1"), then the pivot table column should display "23*" (with the "*" being concatenated into the formula). I was hoping that conditional formatting with the use of a formula option would do that for me in the pivot table!


I love the analysis capabilities of pivot tables, but it is really frustrating to me that you can't use formulae to widen your range of display Values!


Thanks.

 

[Faseeh]

@ Grumpy88


You can add a helper column in your source data with this formula:

=IF(VALUE(A1-INT(A1))=0,A1,A1-VALUE(A1-INT(A1))&"*")


..my last formula will also work: 


=VALUE(A1-INT(A1))=0

Regards,

 

[Grumpy88]

Thanks Faseeh (sorry, I didn't notice your first post above).


Your above formula works perfectly within the source data, but doesn't translate into the pivot table. I've dragged that new helper column into the pivot table (into the Values quadrant) and set its Field Settings to the MAX option - which is what I need), but the rows with the decimal place in their numbers (which should be displaying "23*", etc.) are now displaying as "0". The rows with integers are displaying just the integer, as they should.

 

[Faseeh]

Hi Grumpy88,


So far clueless, I read somewhere in your post that you are adding it in Row Label or perhaps i misread. can you accept something other then pivot table, a normal table that looks like pivot table or something similar??


Regards,