Factor Up and Factor Down Values Formula

Lets say i have to keep my numbers in my spreadsheet above and below a certain number (the result must be 10 or above and less than 60,000,000)

I want to be able to handle to one digit, and round the results . After i used the numbers i will need to factor them either up or down according to the factor used in the initial calculation.

whats the formula ?


thanks

I'm not sure if you want to limit entry of numbers to between 10 and 60,000,000 or if you want to round any numbers already entered to be within this range.
If it's the former, then you can set up Data Validation (Data tab → Data Validation → Allow 'Whole number' or 'Decimal', Data 'between', Minimum '10', Maximum '60000000'). Using the 'Error Alert' tab in the Data Validation window, you can prevent any numbers being entered which aren't within this range, with a warning message given to users if an invalid number is entered.
If you want the latter, then you'll need another column with a formula like this one:

Formula:

Please Login or Register  to view this content.

You can then get the factors used by calculation from the result of this formula with the original number.

Hope that's of some help and that I haven't completely misunderstood your question.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

thankyou but not quite right

what i want is something like .

1. anything below 10 factor up by factor of say 100. So .1 becomes 10. (minimum value is .1)
2. anything above 10 needs to be factored down so the result is between a minimum of 10 and max of 60,000,000 (maximum number can be say 100,000,000)

Each value factored up will have to be factored back down later so i need both formulas as attached files showed (its for use in scroll bar and the scroll bar wont allow any number below 1 and over 60m. No digits either. Thats why it has to be rounded as well

the formula needs to be something like

any number between .1 and 10 factor up by 100
any number between 10 and 1000 factor up by 10
any number between 1000 and 10000 factor down by 10
any number between 10000 and 100000 factor down by 100

etc etc

works !

weird i had this exact same formula but didnt know the formula tests each scenario in sequential order. now i know.

thanks !