Numbers Left And Right Of Decimal.

[jgthtrotih]

Hi,

I have 2 problems..*Formulas Only Please*

Problem #1:

I want to take the number i enter in column "BP" and have it read as "Gallons" (As Shown Below) in column "BQ".


<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 56px;"><col style="width: 132px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>BP</td><td>BQ</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="text-align: center;">DATA</td><td style="text-align: center;">SHOULD READ</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: center;">0.05</td><td style="text-align: center;">0.05 Gallons</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: center;">0.25</td><td style="text-align: center;">1/4 Gallons</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="text-align: center;">0.68</td><td style="text-align: center;">0.68 Gallons</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="text-align: center;">0.75</td><td style="text-align: center;">3/4 Gallons</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="text-align: center;">1</td><td style="text-align: center;">1 Gallon</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="text-align: center;">1.25</td><td style="text-align: center;">1 and 1/4 Gallons</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td style="text-align: center;">2</td><td style="text-align: center;">2 Gallons</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td style="text-align: center;">2.51</td><td style="text-align: center;">2.51 Gallons</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td style="text-align: center;">5.47</td><td style="text-align: center;">5.47 Gallons</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td style="text-align: center;">250</td><td style="text-align: center;">250 Gallons</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="text-align: center;">1000.75</td><td style="text-align: center;">1000 and 3/4 Gallons</td></tr></tbody></table>
Problem #2:

I need to split the "Decimal-ed number" into two columns..i have gotten the formula for the "Left Side" but i need help with the "Right Side". (As Shown Below)


<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 67px;"><col style="width: 42px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>BU</td><td>BV</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="text-align: center;">1000.75</td><td style="text-align: right;">1000</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td style="text-align: center;">24502.52</td><td style="text-align: right;">24502</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td><td style="text-align: center;">16035.26</td><td style="text-align: right;">16035</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td><td style="text-align: center;">25.36</td><td style="text-align: right;">25</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td><td style="text-align: center;">1.2</td><td style="text-align: right;">1</td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>BV14</td><td>=IF(ISERROR(LEFT(BU14,FIND(".",BU14,1)-1))=TRUE,"",LEFT(BU14,FIND(".",BU14,1)-1))</td></tr></tbody></table></td></tr></tbody></table>

Using Windows Xp With Sp2 With Excel 2003.

Pleas Ask Questions If Any.

Thanks!

 

[mikerickson]

Problem 2)

=INT(A1) will return the interger part of the number in A1.
=MOD(A1,1) will return the fractional part.

Problem 1)
I notice that some of the entries are going to fractions and others to decimals.

A custom number format of
# "and" ?/? "gallons"

will display 1.25 as 1 and 1/4 gallons.

But it will also display .68 as 2/3 gallons.

What values should be in decimal and which in fractional notation?

 

[dispelthemyth]

problem 1


right click --> format cell --> custom


enter

#.## "Gallons"

 

[Excelestial]

=right(bu14,len(bu14)-search(".",bu14,1))

 

[AnAnalyst]

Hi there,

If the cell contents are numeric then

=INT(BP4)

will give you the whole number, and

=MOD(BP4,INT(BP4))

will give you the decimal fraction.

HTH

 

[Excelestial]

=IF(ISERROR(LEFT(BU14,FIND(".",BU14,1)-1))=TRUE,"",CONCATENATE(TEXT(LEFT(BU14,FIND(".",BU14,1)-1),"###,###")," Gallons"))

 

[dispelthemyth]

for Problem 2 can you not just do

BU14 - BV14 to get 0.75?

 

[dispelthemyth]

="0."&RIGHT(BU14,(LEN(BU14)-FIND(".",BU14))) to get 0.75
or =RIGHT(BU14,(LEN(BU14)-FIND(".",BU14))) to get 75

 

[jgthtrotih]

Mikerickson,

when i (for example) enter 1000.25 (question 2) the int returns "1000" wihich is right but the mod returns "0.25" i just want "25".

as for (Question 1) i want .25,.50,.75 to return as 1/4,1/2,3/4 only...all other for example .68 i want as ".68".

Dispelthemyth,

All most there..

Excelestial (Great Name BTW),

Prefect Thanks the solves Problem 2's Questions

AnAnalyst,

As mentioned in mikerickson response the mod function returns "0.25" and i need "25"

 

[mikerickson]

=100*MOD(A1,1)

For the number formatting
=TEXT(D15,IF(CEILING(D15,0.25)=D15,"# ""and"" ?/?","0.00")& """ gallon"""&IF(D15=1,"","""s"""))