MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 341
- Office Version
- 2013
- Platform
- 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.
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.