Change the MIN and MAX of a range, and keeping the other figures relative to the originals

[adam.webb]

Hi,

I have a range of 10 numbers for example. The highest number is 900,000 and lowest is 750,000. These represent the 'score' of a batch of products. However, they don't represent the actual 'price range' that i want to market them at. The price range that i want to list at is $300,000 to $400,000. I have entered this price range in other cells.

The 'Score' is calculated by giving value to different characteristics of the product. It considers around 20 different characteristics.

I need to convert the range so the highest number of this range becomes $400,000 and the lowest number becomes $300,000, and the other 8 numbers in between, change also but stay exactly relative to its position in the original set of data.

Somebody may very easily identify what's required... Alternatively, I am happy to provide more information if it's required. This has been bugging me for a month.

Thanks

[Lemice]

Hello,

The first thing you want to try is Filter (found in Data --> Filter), then you can tell it the condition on the price column.

An alternative solution will be adding a helper column that check if the price is within a range of two value that you enter, flag them with 1, then filter by that helper column.

For the best results, please upload a small sample file with dummy data, it will be easier for others to help you also.

[adam.webb]

Thanks a lot

I have uploaded a basic version of the spreadsheet here.

I tried the filter but couldn't make it work...

[Lemice]

Wait, let me rephrase your question: You want to find the ratio between your Max price and $400,000, then multiply every single price in the column with the ratio so that now the max value is $400,000 right?

[adam.webb]

I had gone along those lines to begin with but couldn't quite master it. It would adjust the figures above the median, but not below, and vice versa if i swapped the formula around.

If it were simply changing the MAX figure only, or the MIN figure only, I think it would be easier...

[Lemice]

Hello,

This is a start, check if this sample is the type of result you are expecting

[adam.webb]

Hello,

This is a start, check if this sample is the type of result you are expecting

Thank you however, this is the same formula I used when i initially thought i had solved it! the problem is the gap between the highest and second highest is far too great - that's how i know it's not relative to the initial range.

It may go beyond an excel formula - i may need to speak to a maths genius to identify a ratio or a factor of some sort which can be used as a multiplier...