Check number of digits left of the decimal point

MrDB4Excel

Active Member
Joined
Jan 29, 2004
Messages
348
Office Version
  1. 2013
Platform
  1. Windows
Hello MrExcel Members,
I need to first ascertain how many digits are to the left of the decimal point and then use that data to fill in a multiplier for use in a formula.
Following is the data on a worksheet that produces a speedometer graph:
Columns "A" & "B"
Performance Label Value
Poor 40
Average 30
Good 20
Excellent 10
Total 100

Columns "D" & "E"
Labels Values
>22.1 10
>22.2 10
>22.3 10
>22.4 10
>22.5 10
>22.6 10
>22.7 10
>22.8 10
>22.9 10
>23 10
Total 100

The following gets a current exchange rate from another sheet; shown here in this sheet, SpeedMeter, @ D14:
='Current Rates'!$J$12
The next step in this process; shown here in this sheet, SpeedMeter, @ D18:
=ROUND(D14,4)
The next step; shown here in this sheet, SpeedMeter, @ D19:
=EXTRACTNUMBERS(D18)
The next step; shown here in this sheet, SpeedMeter, @ D20:
=SUM(D19/10000)
The next step; shown here in this sheet, SpeedMeter, @ D21
=SUM((D20-22)*100)
The next step; shown here in this sheet, SpeedMeter, @ D22
=SUM(D21-10)

These preceding six steps gives the speedometer graph the location for the pointer in the graph.
When the exchange rate is for example is 22.6876 then you get the following:
@D14: 22.6876000
@D18: 22.6876000
@D19: 226876
@D20: 22.69
@D21: 69
@D22: 59

When the exchange rate is for example is 22.715 then you get the following:
@D14: 22.715000
@D18: 22.715000
@D19: 22715
@D20: 2.27
@D21: -1973
@D22: -1983
When the exchange rate is for example is 22.715 then the result does not work because the divider in D20 is 10,000
If the divider is changed to 1,000 then the results are good as seen here:
@D14: 22.715000
@D18: 22.715000
@D19: 22715
@D20: 22.72
@D21: 72
@D22: 62

Hopefully this is enough information to get to the point.
Depending on the result showing in D19 (=EXTRACTNUMBERS(D18)) I need the divider in D20 to change automatically to cause D21 to become only two digits to the left of the decimal point as seen in the preceding example.
My initial thought is to get a count of the digits to the right of the decimal point in D18 and this would give the divider the appropriate count of zeros after 1, so if the count of digits to the right of the decimal point in D18 is four digits then the divider would be 10,000 (four zeros after the digit 1.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Too much info for me to understand.

Given a number, e.g. 1234.56 and you simply want the number of digits to the left of the decimal point then just use

=LEN(INT(A1))

where A1 contains the value
 
Upvote 0
Thanks Special-K99. This helps to get the number of digits.
I stumbled upon a solution as follows so I assume we can close this thread?
='Current Rates'!$J$12



=ROUND(D14,4)
=EXTRACTNUMBERS(D18)
=SUM(D19/D30)
=SUM((D20-22)*100)
=SUM(D21-10)


=LEN(RIGHT(D18,LEN(D18)-FIND(".",D18)))
=D25
=D29&REPT("0",D28-LEN(D29))
=SUM(D26+1)
=SUM(D26/D26)
=D27
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top