Decrease value by fixed percentage across columns

[w0kkie]

Hello! I have an interesting situation I've been poking at for a few days now to no avail, so it would be great if anyone else has tackled a similar problem before and knows of an elegant solution.

Here's the situation:
I have a table, let's say 10row x 8col. The rows represent a list of products, and cols represent the sale year.
Each product(row) has a starting price (price it starts at). The list of starting prices is one cell over, in the same rows.
There are two cells I need to be able to set manually, which are: Reduction amount (percentage), and reduction length (number of years the reduction should continue for).

I've gotten halfway there by using a numerical index and comparing it to the reduction duration value, which gets the the behavior I want as far as applying to price reductions for the correct number of years, but it only works if I start all the prices on year 1. I'm stuck on how to get some products to start on different years, leaving preceding years blank, and still have the price reduction logic behave correctly.

One last caveat - I can't use macros, because some users of this sheet are not permitted to "enable macros" on a workbook while having certain other files open, which is common.

For visual reference, here's an example of how it would look if it were working properly:

And here's how it looks now, as far as I've been able to get:

You can see the cell references and the formula I'm using in the screenshot, but for convenience here it is in text as well. Cell F5 contains:

=IF($C$2-E$3>0,E5*(1-$C$3),E5)

I appreciate any ideas!

 

[KRice]

Here is one approach:

Book1_20200530.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2		Red Dur	3 yrs
3		Red Amt	5%
4		Product	Price	Launch	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7
5		ID #1	100	Yr 1	$100.00	$95.00	$90.25	$85.74	$85.74	$85.74	$85.74
6		ID #2	200	Yr 3			$200.00	$190.00	$180.50	$171.48	$171.48
7		ID #3	300	Yr 1	$300.00	$285.00	$270.75	$257.21	$257.21	$257.21	$257.21
8		ID #4	200	Yr 2		$200.00	$190.00	$180.50	$171.48	$171.48	$171.48
9		ID #5	150	Yr 2		$150.00	$142.50	$135.38	$128.61	$128.61	$128.61
10		ID #6	125	Yr 1	$125.00	$118.75	$112.81	$107.17	$107.17	$107.17	$107.17
11		ID #7	250	Yr 3			$250.00	$237.50	$225.63	$214.34	$214.34
12		ID #8	200	Yr 2		$200.00	$190.00	$180.50	$171.48	$171.48	$171.48
Sheet6

Cell Formulas
Range	Formula
E5:K12	E5	=IF(RIGHT($D5,LEN($D5)-3)=RIGHT(E$4,LEN(E$4)-5),$C5,IF(RIGHT(E$4,LEN(E$4)-5)<RIGHT($D5,LEN($D5)-3),"",IF(COLUMNS($E5:E5)-1<RIGHT($D5,LEN($D5)-3)+LEFT($C$2,LEN($C$2)-4),D5*(1-$C$3),D5)))

 

[w0kkie]

Here is one approach:

Hi KRice, thanks for the reply. I tried that formula in the range, and it correctly sets the first year price for each row. More progress.!

Unfortunately though, every other cell gets a #VALUE! error. I applied the formula to cell E5, and then auto-filled to the right and then down. I also pasted it across the entire range E5:K12. Same result any way I spin it. Any ideas?

 

[w0kkie]

Here's what it looks like after applying the formula: (showing cell F5 highlighted, same as before)

 

[KRice]

Yes, generally it's a bad idea to mix the number you want to use with a word (or units) in the same cell. That requires some extra effort to extract the number of interest. In your recent post, I suspect the issue is related to cell C2. Note in your original post, you had "3 Yrs" with a space between 3 and Yrs. In this recent post, you have "3yrs", so the formula isn't correctly extracting the "3". Try adding a space and see if that fixes the problem.

 

[w0kkie]

Figured it out! The cell C2 contains the value "3" in my sheet, using custom formatting to display "yrs" after the cell value. Once I inspected your formula more closely I realized you were considering the cell C2 to contain the value "3 yrs". Once I realized that, I just switched up the cell formatting to test it, and it works!

I'll change it up based on the custom formatting rules that the sheet has, and I should be set. Thank you

 

[KRice]

Not to put too fine of a point on it, but here is a slightly revised version that eliminates the compound numbers/words...and the formula is greatly simplified:

Book1_20200530.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2		Red Dur (y)	3
3		Red Amt	5%		Year
4		Product	Price	Launch (y#)	1	2	3	4	5	6	7
5		ID #1	100	1	$100.00	$95.00	$90.25	$85.74	$85.74	$85.74	$85.74
6		ID #2	200	3			$200.00	$190.00	$180.50	$171.48	$171.48
7		ID #3	300	1	$300.00	$285.00	$270.75	$257.21	$257.21	$257.21	$257.21
8		ID #4	200	2		$200.00	$190.00	$180.50	$171.48	$171.48	$171.48
9		ID #5	150	2		$150.00	$142.50	$135.38	$128.61	$128.61	$128.61
10		ID #6	125	1	$125.00	$118.75	$112.81	$107.17	$107.17	$107.17	$107.17
11		ID #7	250	3			$250.00	$237.50	$225.63	$214.34	$214.34
12		ID #8	200	2		$200.00	$190.00	$180.50	$171.48	$171.48	$171.48
Sheet6 (2)

Cell Formulas
Range	Formula
E5:K12	E5	=IF($D5=E$4,$C5,IF(E$4<$D5,"",IF(COLUMNS($E5:E5)-1<$D5+$C$2,D5*(1-$C$3),D5)))

 

[KRice]

Thanks for the feedback and the additional detail...I'm glad you got it working. I missed the small difference (the space in C2) in the two examples in your original post, but in this case, that space matters.

 

[w0kkie]

Thanks for the clarification! Looks like we both posted at about the same time, haha.

And I agree, I generally avoid mixing characters in a cell if it's used as a reference, which is why in the actual sheet I'm working with the values are numerical only, and the characters are appended using custom cell formatting, just to improve readability and keep the table compact without having too many header rows/columns or notes for the user.

In the example tables I created for my original post, I made one tiny oversight, as you pointed out above - one of the tables had the custom formatting with a number, as my real sheet does, and the other was simply typed in the cell.

 

[mikerickson]

This version assumes layout like the OP, with "Red.Dur" in A1 and "yr 1" in column C.

=IF(RIGHT($C4,1)=RIGHT(D$3,1),$B4, L4*(1-IF((RIGHT(D$3,1)+0)<=(RIGHT($C4,1)+LEFT($B$1,1)),$B$2,0)))