Search a list of numbers

[Mike_Cook]

Can you help ? I have a list of numbers that i need to change based on a number given to me. In this example the number given is 2200. I know that 6000 has to be changed to 2200 and because 2000 is lower than 2200 this remains the same. The remaining numbers need to change in equal increments based on the difference between the given number, and the number that is lower than the given number in the list which is 200.  So because we are changing 5 numbers 200/5 = 40, but the first entry must always be the given number

6000

5000

4000

3000

2500

2000

It would look like this. 

6000 change to 2200

5000 change to 2160

4000 change to 2120

3000 change to 2080

2500 change to 2040

2000 unchanged

The unknown is the number of numbers that need to be changed , for example if the list included another number that was higher than 2000 but lower than 2200 the difference would have to be divided by more, in this example it would be by 6 

Any help appreciated 

[#2]

Hello Mike, 

Perhaps, you need this formula in B3 of the attached file: 

=IF(A3=MIN(A:A),
A3,
B2-((B$2-MIN(A:A))/(COUNT(A:A)-1)))

[#3]

@Twifoo  Thank you, let me give you some context, I work for an energy company, as you may know we sometimes estimate meter readings to create a bill. we bill every 3 months - we call this a quarterly bill. If we estimate a bill and a customer calls with a meter read that is lower than we have estimated we have to amend the bill, sometimes we have to amend multiple bills if we have not taken or been given a meter reading.

For example here are the estimated readings

Q4 2019/18 - 6000

Q3 2019/18 - 5000

Q2 2019/18 - 4000

Q1 2019/18 - 3000

Q4 2018/17 - 2500

Q3 2018/17 - 2000

Q2 2018/17 - 1500

Q1 2018/17 - 1000

Q4 2017/16 - 0500

Q3 2017/16 - 0250

Q2 2017/16 - 0100

Q1 2017/16 - 0050

The customer calls and tells us the meter reading is 2200, this means we have to change the readings for all the bills that have been estimated higher than this number. in this example Q3 2018/17 is the first bill in the list that is lower than 2200 so this remains the same. We have to change all the bills above this one. We know that the Q4 2019/18 will be 2200. This means we have to change these bills

Q3 2019/18 - 6000

Q3 2019/18 - 5000

Q2 2019/18 - 4000

Q1 2019/18 - 3000

Q4 2018/17 - 2500

To calculate the estimated reads for these bills we calculate the difference between the reading for the bill that didn’t need to be changed, Q3 2018/17 and the reading the customer gave 2200. in this example the difference is 200 (2200 - 2000) and the number of bills that need to be amended is 5, giving us 40.  ( 2200-2000 / 5 )

What we are doing is working out the average usage during the period we are amending the bills and adding this average usage to each amended bill. This table explains 

Q4 2019/18 - 6000 to 2200 Changed 

Q3 2019/18 - 5000 to 2160 Changed 

Q2 2019/18 - 4000 to  2120 Changed 

Q1 2019/18 - 3000 to  2080 Changed 

Q4 2018/17 - 2500 to 2040 Changed 

Q3 2018/17 - 2000 to 2000 No Change 

[#4]

@Twifoo  this is how far i have got , It gets a bit more complicated, if more than 4 weeks has passed since the date of the last bill we have to also include an information only reading. so using this example, the Q4 2019/18 was generated on the 1 January 2019. The customer called on the 15 February 2019 with the reading of 2200. What this means that we now have to divide the difference by 6. 5 for the amended bills and another information only read

[#5]

How did you arrive at 6.5? Please provide sample results in your attached file.

[#6]

@Twifoo sorry, its not 6.5. its 6 or 5. if the last bill was over 4 weeks ago, we have to add the latest read as an information only read, if the bill is less than 4 weeks we amend the bill. This means that depending on when the last bill was produced we have to divide the difference by 6 or 5 see update to attached spreadsheet 

[#7]

If the last bill is more than 4 weeks old, there is no need to amend the last 5 bills. Why divide by 6, if there is nothing to amend, anyway?