Mar 18 2019 08:51 PM
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
Mar 18 2019 09:43 PM
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)))
Mar 18 2019 10:30 PM
@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
Mar 18 2019 10:38 PM
@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
Mar 18 2019 11:03 PM
Mar 18 2019 11:35 PM
@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
Mar 19 2019 12:11 AM