# Decimal Point - Dynamic Conditional Formatting Formula?

Jan 26, 2014
Is it possible to Conditionally Format using the (Custom) Data Validation Tool one or both of the following conditions:-

1. Force the user to enter the correct decimal point position for a given currency :-

The are 3 different decimal point locations location's - 0.87624 / 123.123 / 1273.00 There are always 6 numerals We have a table to lookup which specifies where the decimal point is on all currencies

CURRENCY AUDUSD< List is linked to a table

Price - High 0.87744< Decimal point dependant on chosen currency pair chosen

Price - Low 0.86596

Price - Close 0.86823

2. As above but numbers once entered will be formatted accordingly to the correct decimal place to the corresponding currency

View 12 Replies
ADVERTISEMENT
Jun 10, 2009

I need to convert a column of numbers currently formatted with 2 decimal places e.g. 112.12 to 4 decimal places (without the decimal point). I need the end result to be 1121200. I've tried a few different suggestions given on the forum previously but can't seem to retain the 4 decimal places that I require.

View 4 Replies
View Related
Aug 8, 2008

I am trying to create a unique sample code by putting together the values of other cells that a user will input. It's all working well apart from the last part, where I am trying to include a decimal number. I want the decimal number to appear without the central "." and in a four digit format. e.g. 2.5 would appear as 0250, 14.25 would appear as 1425. This is the formlua I am using currently:

=IF(ISBLANK(B4),"",IF(LEFT(C4,1)="w",(B4&"-"&TEXT(F4,"YYMMDD")&C4&TEXT(G4,"HHMM")),(B4&"-"&TEXT(F4,"YYMMDD")&C4&LEFT(TEXT(H4,"00"),2)&RIGHT(TEXT(H4,"00"),2))))

However, where the value of H4 is 2.5, I am getting a result of 0303 (I've put this part in bold). I have attached a small spreadsheet to aid understanding.

View 2 Replies
View Related
Jan 11, 2013

Is it possible to set conditional formating so the cell is filled with color only if a value in the cell has more then 2 decimal digits?

It doesn't matter if I set the cell formating to show only 2 decimal digits, it still contains a value with more digits and that causes an error. I need to make it visible for viewers.

The value itself is being pasted by users so they don't even know it has more then 2 decimal digits as they paste the value with formating as well.

View 8 Replies
View Related
Jun 21, 2009

I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I can’t seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesn’t.

View 2 Replies
View Related
Aug 19, 2009

is there any way to use the right function to return any data to the right of the decimal point?

eg 117.22 would be .22

108.1225 would be .1225

102.358 would be .358 etc

View 5 Replies
View Related
Jan 17, 2009

Would like to do the following for our school,

In cell A1 there is a number, for instance: 12.12345

Based on that number, the 3rd number after the decimal is important.

If the 3rd number after the decimal is “x” then the contents returned in b2 is a text value ( like high school ) .........

View 9 Replies
View Related
May 12, 2007

During finalization I have to convert the numbers into millions. But due to rounding factor, the total doesnot match with the displayed fig. how can I sum only upto two digits of the fig? A sample file is attached.

View 9 Replies
View Related
Jan 17, 2009

I need to add a decimal point to a column of numbers. For example, where it says 126 needs to be changed to 1.26, 3035 changed to 30.35, 13593 to 135.93 and so on. Can this be done automatically or with a formula?

View 2 Replies
View Related
Jul 30, 2009

This problem just appeared recently, as if out of the blue, and it seems to affect all of my old work sheets as well as new ones. I am using 2002 version. When I input any number in any cell the program automatically inserts a decimal point. Thus 1 becomes .1, 12 becomes 1.2, 123 becomes 12.3 etc. I have tried formatting the cells to "general" and to to "number" with no change. The thing that really gets me is that old saved worksheets exhibit this behavior as well. I can work around the problem by inputting 1. or 12. which then gives me the entry I am after but I've never needed to do this before.

View 2 Replies
View Related
Aug 20, 2009

a1 -> 2.333.33 "i want this to return as 2,333.33"

a2 -> 33.33 "i want this to retun 33.33"

a3 -> 45.555.55 "i want this to return 45,555.55"

i already asked this question and someone gave me this code -> substitute(a1,".","",1) which return to 2333.33 but the problem is for the a2 if i use the code it will return as "3333" which read as "3,333".

Is there any way that we can determine if the values have two decimal point before it will run the command?

View 3 Replies
View Related
Aug 25, 2009

I've just imported a file into a UK version of Excel from a German made datalogger. The decimal points (which should look like this 0.0) all appear as comas (0,0) with the result that Excel can't read them.

View 5 Replies
View Related
Jan 31, 2014

I need to allow only in textbox1 Numbers & decimal Point.

View 4 Replies
View Related
Apr 6, 2012

I have one problem in my data

In the amount Column, the amount looks like

10000, 10025, 20025 and so on

The amount should be

100.00, 100.25, 200.25

The decimal point is missing,

Any formula for the decimal point to appear in every last two digits

View 2 Replies
View Related
Aug 19, 2013

I am looking for a formula that will calculate the number after the decimal point, for example: A1=0.4656162 I want to multiple the number after the decimal point by A2=60, is there a simple formula to enable me to do this as I have to perform this across a large number of cells.

View 2 Replies
View Related
Nov 25, 2006

Is it possible to enter a number into a cel as: 12345 and have it display as 123.45.

View 10 Replies
View Related
Aug 3, 2006

Basically, I want to format a group of cells to display 1 decimal figure if the number is not a whole number. If the number is a whole number (or if the rounded first decimal place is 0) I want it to display no decimal.

View 9 Replies
View Related
Nov 16, 2008

I have just started a job and given a task to input some timesheets that were done manually. The problem is that I used decimal point instead of the colon. When I try and format the cells I do not get the real time.

Here is an example: ....

View 7 Replies
View Related
Sep 25, 2013

How to make calculation in excel without decimal point automatically. I use this formula =MIN(360,30*F42/360) . Total displayed 144. By right 142

DAY

FORMULA

RESULT

BY RIGHT

361

=F12/360*30

30.08

30

365

=F13/360*30

30.00

30

365

=F14/360*30

30.00

30

366

=F15/360*30

30.08

30

269

=F16/360*30

22.11

22

=MIN(360,30*F42/360)

144

142

View 6 Replies
View Related
Jul 12, 2014

So how can it be that Excel calculates Pi()/90*90=Pi(); but Pi()/90+Pi()/90+Pi()/90......90 times is 0.000000000000033527. And why is the number zero out to 14 decimal places not being returned as 0?

I need to know exactly when a series of sequentially increasing radians = Pi(). The length of the series is dynamic and can be as few as 6 and as many as 120. And right now Excel is not cooperating.

Here is what excel thinks Pi()/90+Pi()/90+....90 times =

3.14159265358980000000

3.14159265358979000000 (this is how excel displays Pi() out to 20 decimal places)

So Excel thinks the two numbers are not equal to each other, and what could be more interesting is that Excel thinks the difference is

0.00000000000000355271

Even after copying and pasting both numbers as values out to 20 decimals. Excel still thinks the difference is 0.00000000000000355271

How is that possible?

I have Excel 2013. Is there some sort of bug here? I had posted earlier about major headaches when using natural log. Random cells keep getting forced to 14, 15, sometimes 16 or 17 decimals. No rhyme no reason. I can custom format every cell in worksheet to 2 decimals, and as soon as I hit F9... ############# all over the place. At least with Pi() the decimals are under control, but the math is not right. Pi()/x + Pi()/x.....x times = Pi().

View 2 Replies
View Related
Aug 22, 2008

Is there anyway to be able to bring decimal point forward without affecting the numbers using any formatting, which will still be there even after refreshing the query.

For example, 0.0531 to 53.1

View 9 Replies
View Related
Mar 31, 2009

I have a column that has values ranging anywhere from 1 to 999,999 and I need a way in vba to take whatever value is in the specified cell and place a decimal point at the far left. Example:

change 175526.34 to .17552634

change 376.1 to .3761

change 22987.254 to .22978254

when the code is run I need the selected number to begin with the decimal point.

See, the tricky part is that the length of the number varies and I cant just divide by X to shift so many places to the left..

View 9 Replies
View Related
Jul 10, 2012

I have an Excel 2007 Workbook which is refreshed by a Web Query. I have dynamic named range defined as "Manager" which I've confirmed is correctly identified. This range includes only one column and is formatted as text.

When I try to reference this range in my conditional formatting "refers to" formula, all cells are recognized as blank even though the range clearly contains many cells that are not. For example, there are 90 records containing the initials "PD". If I use the formula =Manager="PD" in my conditional formatting, nothing changes. If I change it to =Manager="" then ALL records are formatted, including those that are not blank.

Stranger still, if I enter the formula =COUNTIF(Manager,"PD") into and empty cell in my worksheet those 90 records are counted correctly. Which leads me to believe it isn't about the data. Conversely, =COUNTIF(Manager,"") returns the correct count of only cells that are, in fact, blank.

I've tried using the OFFSET formula defining my range in place of the name itself for my conditional format formula to no avail.

View 2 Replies
View Related
May 22, 2012

Building a formula to calculate the total downtime for a specific configuration item (in this example: Wintrack).

See table below:

Task.NumberConfiguration ItemDowntimePriorityINC0028900Wintrack602INC0028900Data Tree201INC0028901Wintrack151INC0028903FAST1901INC0028904Wintrack102INC0028905Wintrack53

The table above will vary in row numbers every month so I would like to use dynamic range. I will be calculating total downtimes for each configuration item every month and would like those totals reflected on a separate Excel sheet. .

View 4 Replies
View Related
Apr 15, 2012

I have to make a excel document in which I have length and width in feet and inch format.

E.g. 10.01 in which 10 is feet and .01 is inch

I have all the length and width values in the above format. And what I want to do is convert the inch value (10.01) to feet value (.01=.08 feet) .

Just like the calculator here does.. [URL] ........

Like

10.01=10.08

10.02=10.16

10..03=10.25

...and so on...

Here is table of conversions from inches to decimal feet. But I don't know how to get a formula for this in excel...????

Inch Decimal of a Foot

1 inch 0.0833

2 inches 0.167

3 inches 0.250

4 inches 0.333

5 inches 0.417

6 inches 0.500

7 inches 0.583

8 inches 0.667

9 inches 0.750

10 inches 0.833

11 inches 0.917

12 inches 1.000

View 7 Replies
View Related
Feb 16, 2007

I am looking to create a dynamic bubble chart. To do this I am using offset to create the series formula which without listing all the data names turns out to be something like this (where 297 will adjust to the number of rows with data): =SERIES(Project!$A$5:$A$297, Project!$J$5:$J$297,Project!$P$5:$P$297,2,Project!$Q$5:$Q$297)

My problem is that by creating the chart in this fassion I am unable to get a unique name for each data point. For example, if row A looks like this,

A5: 1

A6: 2

A7: 3

each data point will be named 1 2 3. How do I either create a new series for each row dynamically or get the specific name from column A to associate with the correct data point?

View 2 Replies
View Related
Mar 9, 2009

Have 2 conditional formattings going on in a database query and need to apply two more conditions but only have 1 conditional formatting left. I am trying to highlight everything in a row that contains either "HCAT" or "HCAL" in column D. So far I have this,

=$D2=OR(HCAT,HCAL)

but its not highlighting anything, any clues?

View 3 Replies
View Related
Jul 18, 2012

I simply need to have column A be red if value is < Column B value and green if it is >. The problem is that if the value in column A is .00000004 and column B is .00000005, it turns red. Example is the first three numbers below the headers....they each are 0%, yet the value on the left turns red because it is .0000005 more than the value on the right. I just want the colors to reflect the numbers which show up as below. Is there a way to do this?

2011 % of PDD

2011 % of Total $

0%

0%

0%

0%

0%

0%

[code].....

View 1 Replies
View Related
Feb 1, 2008

1. I have a range of random numbers within a column, i.e. A1:A9

2. A specific number will appear in a cell at the end of that column, i.e. A11

3. I need to see via conditional formatting which of the above cells add up to that number.

I am aware that given the numbers in A1:A9, it is possible to have more than one combination of numbers that add up to A11. I would need to see those combinations as well identified using Conditional formatting.

View 9 Replies
View Related
Mar 20, 2007

I am referencing a cell in order to change the background color of a row of cells if the number in column B is = to the number in a specific cell. The formula used in the conditional formatting is =$B73=$B$10. The reference cell $B$10 contains the following formula =INT(MAX('Assumptions & Inputs'! F8:F9))

The conditional formatting worked fine when I used that formula. I had to change the formula in cell $B$10 so that it would meet a new criteria. When I did that the conditional formatting would not change the color of the row. The new formula that I put in the reference cell $B$10 is

='Assumptions & Inputs'!$D$8+MAX('Assumptions & Inputs'!$F$8-'Assumptions & Inputs'!$D$8,'Assumptions & Inputs'!$F$9-'Assumptions & Inputs'!$D$9)

View 5 Replies
View Related