How to find the number?

[Eric]

Does anyone have any suggestions on how to find the number?

Example 1:
There is a number 19650 in cell A1, I would like to fit this number below
2025, therefore, it should return 1965 in cell B1.

Example 2:
There is a number 25624 in cell A1, I would like to fit this number below
2025, therefore, it should return 256 in cell B1.

Example 3:
There is a number 10.065 in cell A1, I would like to fit this number below
2025, therefore, it should return 1006 in cell B1.

Does anyone have any suggestions on how to set the formula to do it?
Thanks in advance for any suggestions
Eric

 

[dlw]

what do you mean by "fit this number below 2025"?

 

[Luke M]

I don't understand example 3. Isn't 10.065 less than 2025?

For the first two examples, perhaps:
=IF(VALUE(LEFT(A1,4))<=2025,VALUE(LEFT(A1,4)),VALUE(LEFT(A1,3)))

 

[Ron Rosenfeld]

Does anyone have any suggestions on how to find the number?

Example 1:
There is a number 19650 in cell A1, I would like to fit this number below
2025, therefore, it should return 1965 in cell B1.

Example 2:
There is a number 25624 in cell A1, I would like to fit this number below
2025, therefore, it should return 256 in cell B1.

Example 3:
There is a number 10.065 in cell A1, I would like to fit this number below
2025, therefore, it should return 1006 in cell B1.

Does anyone have any suggestions on how to set the formula to do it?
Thanks in advance for any suggestions
Eric

If by "fit below 2025" you mean multiply the number in A1 by the power of 10
that gets it as close to 2025 as possible, without going over, then this
**array-entered** formula does that (and returns the values you show in your
examples):



This formula must be **array-entered**:

=INT(A1*10^(10-MATCH(TRUE,A1*10^(10-ROW(INDIRECT("1:20")))<=2025,0)))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
--ron

 

[Eric]

Thank everyone very much for suggestions
Eric

I don't understand example 3. Isn't 10.065 less than 2025?

For the first two examples, perhaps:
=IF(VALUE(LEFT(A1,4))<=2025,VALUE(LEFT(A1,4)),VALUE(LEFT(A1,3)))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

 

[Dana DeLouis]

Just guessing here...

=INT(A1/POWER(10,CEILING(LOG(A1/2025),1)))

= = =
Dana DeLouis

 

[Ron Rosenfeld]

Just guessing here...

=INT(A1/POWER(10,CEILING(LOG(A1/2025),1)))

= = =
Dana DeLouis

I like the approach a lot, but it returns an error when A1=10.065.

Perhaps:

=INT(A1/POWER(10,INT(LOG(A1/2025)+1)))

--ron

 

[Dana DeLouis]

Thanks Ron. I think I also meant to use Log10() also.
Dana