Excel smoothing formula

[eddster]

Hi, I wonder if anybody could assist me in a smoothingformula I am struggling with,


In this scenario I wish to take the replacementvalue/period of an item based on several inputs and in some instances smooththe resulting smooth the output over either 3,5,7 periods equally eachside of the period that the item would be due

example below

The top table is a working version i.e. I have the necessary formulas togive the required output in columns H - AK however in this version no form ofsmoothing is being applied


In the 2^nd table I have added in a smoothingvalue and hardcoded the result as I would expect to see them IF I had the formula ?





The formula I am currently using for cell J6 in the toptable is


=(IF($C6="",0,IF(AND($D6="N/A",$C6=J$2),$G6,IFERROR(IF($C6>J$2,0,((MOD(J$2-$C6,$D6)=0)*$G6)),0))))= £4000


I need the formulas that will give the desire £1333.33output in J6 on the bottom table and spread the costs either side accordingly

A1	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
2	Items	1st replacement period	frequency	quantity	cost	Total cost per replacement cycle	smoothing	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
3	pencils	1	1	1000	0.1	100		100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
4	erasers	4	N/A	400	1	400		0.00	0.00	0.00	400.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
5	books	2	4	50	10	500		0.00	500.00	0.00	0.00	0.00	500.00	0.00	0.00	0.00	500.00	0.00	0.00	0.00	500.00	0.00	0.00	0.00	500.00	0.00	0.00	0.00	500.00	0.00	0.00	0.00	500.00	0.00	0.00	0.00	500.00
6	cupboard	2	10	10	400	4000		0.00	4000.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	4000.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	4000.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
7	table	3	15	10	150	1500		0.00	0.00	1500.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	1500.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
8
9
10
11	Items	1st replacement period	frequency	quantity	cost	Total cost per replacement cycle	smoothing	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
12	pencils	1	1	1000	0.1	100	1	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00	100.00
13	erasers	4	N/A	400	1	400	3	0.00	0.00	133.33	133.33	133.33	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
14	books	2	4	50	10	500	3	166.67	166.67	166.67	0.00	166.67	166.67	166.67	0.00	166.67	166.67	166.67	0.00	166.67	166.67	166.67	0.00	166.67	166.67	166.67	0.00	166.67	166.67	166.67	0.00	166.67	166.67	166.67	0.00	166.67	166.67
15	cupboard	2	10	10	400	4000	3	1333.33	1333.33	1333.33	0.00	0.00	0.00	0.00	0.00	0.00	0.00	1333.33	1333.33	1333.33	0.00	0.00	0.00	0.00	0.00	0.00	0.00	1333.33	1333.33	1333.33	0.00	0.00	0.00	0.00	0.00	0.00	0.00
16	table	3	15	10	150	1500	7	214.29	214.29	214.29	214.29	214.29	214.29	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	214.29	214.29	214.29	214.29	214.29	214.29	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	214.29	214.29

<tbody>
</tbody>

 

[Eric W]

Try:

r><
td style="text-align: right;background-color: #FAFAFA ;;">166.6667

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1
2		Items	1st replacement period	frequency	quantity	cost	Tot al cost per replacement cycle	smoothing	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
3		pencils	1	1	1000	0.1	100	1	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
4		erasers	4	1000	400	1	400	3	0	0	133.333333	133.3333	133.3333	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5		books	2	4	50	10	500	3	166.666667	166.6667	166.666667	0	166.6667	166.6667	166.6667	0	166.6667	166.6667	166.6667	0	166.6667	166.6667	166.6667	0	166.6667	166.6667	166.6667	0	166.6667	166.6667	166.6667	0	166.6667	166.6667	166.6667	0	166.6667
6		cupboard	2	10	10	400	4000	3	1333.33333	1333.333	1333.33333	0	0	0	0	0	0	1333.333	1333.333	1333.333	0	0	0	0	0	0	0	1333.333	1333.333	1333.333	0	0	0	0	0	0	0
7		table	3	15	10	150	1500	7	214.285714	214.2857	214.285714	214.2857	214.2857	214.2857	0	0	0	0	0	0	0	0	214.2857	214.2857	214.2857	214.2857	214.2857	214.2857	214.2857	0	0	0	0	0	0	0	0	214.2857

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
<td sty
le="text-align: right;;"></td>
<td sty
le="text-align: right;background-color: #FAFAFA ;;">100</td>
<td st
yle="text-align: right;background-color: #FAFAFA ;;">0</td>
</tbody>

Sheet1​

Worksheet Formulashead> Cell Formula I3 =IF(OR(MOD(I$2-$C3,$D3)<=$H3/2,MOD(I$2-$C3,$D3)>=$D3-$H3/2),$G3/$H3,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Note that I replace the N/A in D4 with 1000 (or any really large number). Sorry about the formatting.