boltandnut
New Member
Hello,
I have a online shop and I want to add new pack sizes and pricing to my existing excel spreadsheets. I have attached a example of one spreadsheet i am working on where i need help with creating new sell prices using excel formulas. I have 25 sheets in total, each sheet varies between 5000 rows to 35,000 rows.
1. Column I is the existing pricing which I cant change
2. Column H is the final price i created using my system which is clumsy and slow, to get to those prices I had to copy and paste the data that are in rows 19,20,21,22,23, column J,K,L,M manually for 14,000 rows, then go back and review each one and make adjustments.
3. To get to the final price i take into account column O and Q.
4. Column O is the difference between the price and the previous price.
5. Every product starts with a pack size of 1, the last pack size varies on the product.
What i want to be able to do is to fill the gaps in column I. The prices need to descend.
Example 1.
a. Row 13, column I - I always want the price to be in between row 12 and 14 with a 5% buffer but is always < than Row 14. So this would apply in any instance where there is only 1x gap
b. The pricing for rows 15 - 18, needs to slightly descend and always be > than the price in row 19
c. The pricing for rows 20 - 23 to descend.
The prices in Column J are where i want to be.
Example 2.
a. Rows 84 - 91. Because there is no end price, then the formula can calculate sell with a slow descend. Like i have done with my final price in Column J. You can see how the pricing descend slowly in column O from rows 84 - 91
I will still have to go back and review the pricing to make final adjustments but if i can use a few formulas where i can create most of the prices and then just tweak a few it would be most helpful.
I have a online shop and I want to add new pack sizes and pricing to my existing excel spreadsheets. I have attached a example of one spreadsheet i am working on where i need help with creating new sell prices using excel formulas. I have 25 sheets in total, each sheet varies between 5000 rows to 35,000 rows.
1. Column I is the existing pricing which I cant change
2. Column H is the final price i created using my system which is clumsy and slow, to get to those prices I had to copy and paste the data that are in rows 19,20,21,22,23, column J,K,L,M manually for 14,000 rows, then go back and review each one and make adjustments.
3. To get to the final price i take into account column O and Q.
4. Column O is the difference between the price and the previous price.
5. Every product starts with a pack size of 1, the last pack size varies on the product.
What i want to be able to do is to fill the gaps in column I. The prices need to descend.
Example 1.
a. Row 13, column I - I always want the price to be in between row 12 and 14 with a 5% buffer but is always < than Row 14. So this would apply in any instance where there is only 1x gap
b. The pricing for rows 15 - 18, needs to slightly descend and always be > than the price in row 19
c. The pricing for rows 20 - 23 to descend.
The prices in Column J are where i want to be.
Example 2.
a. Rows 84 - 91. Because there is no end price, then the formula can calculate sell with a slow descend. Like i have done with my final price in Column J. You can see how the pricing descend slowly in column O from rows 84 - 91
I will still have to go back and review the pricing to make final adjustments but if i can use a few formulas where i can create most of the prices and then just tweak a few it would be most helpful.