Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to find the number?

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
dlw dlw is offline
external usenet poster
 
Posts: 510
Default How to find the number?

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

"Eric" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,722
Default How to find the number?

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)),VALU E(LEFT(A1,3)))
--
Best Regards,

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


"Eric" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default How to find the number?

On Tue, 31 Mar 2009 07:12:07 -0700, Eric
wrote:

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
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to find the number?

Thank everyone very much for suggestions
Eric

"Luke M" wrote:

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)),VALU E(LEFT(A1,3)))
--
Best Regards,

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


"Eric" wrote:

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



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 947
Default How to find the number?

Just guessing here...

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

= = =
Dana DeLouis


Eric wrote:
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

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default How to find the number?

On Tue, 31 Mar 2009 20:46:00 -0400, Dana DeLouis
wrote:

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
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 690
Default How to find the number?

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


Ron Rosenfeld wrote:
On Tue, 31 Mar 2009 20:46:00 -0400, Dana DeLouis
wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find previous number and find next number in column DoubleZ Excel Discussion (Misc queries) 4 March 4th 09 09:51 PM
Need Cells.find to find first number in a row which is 8000 Kasper Excel Discussion (Misc queries) 9 December 15th 08 03:10 PM
find the frequency a number appears with another number within a s greenmachine Excel Worksheet Functions 10 September 11th 07 03:19 AM
countif formula to find the occurances of a number that is greater than one number but less than another steveo Excel Discussion (Misc queries) 3 July 8th 06 02:04 AM
How do I find the total number of the same number/letter in a row Anne-Marie Excel Discussion (Misc queries) 9 October 21st 05 10:22 AM


All times are GMT +1. The time now is 12:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"