merging formula - assitance

[eddster]

Mr Excel Query

I currently have two formulas that I am trying to combine sothat the combined formula provides both outputs required

I wonder if anyone could assist on the basis of info provided below

The formal below are the 2 current versions I have in a cell W7 I would like to combine them to achieve both function from the one formula

1. Formula 1 - this formula takes a value (£) and period (Years)and if they match the Year column [W] it applies the cost into that cell, ifthe frequency is every 5 years i.e. 5 it will then apply the cost in the nextperiod due. i.e. £7,500 in 2019 with a frequency of 5 will apply the £7,500cost into the 2019 column and then the 2024 column.

Where
AL7 = year of work
H7 = frequency
W6 = column year
T7 = work cost value (spread value so total divided by spreadperiod which is a cell S7)

=IF($AL7="",0,IF(AND($H7="N/A",$AL7=W$6),$T7,IFERROR(IF($AL7>W$6,0,((MOD(W$6-$AL7,$H7)=0)*$T7)),0)))

1. Formula 2 - I also have another formula which works for thiscell W7 which applies a different calculation, in this case the formula looksat the year and costs but also looks at a spread value which then spreads thecost over the period of time stated i.e. if it was 2019 with a spread of 3 itwould apply the value 0ver years 2019/2020/2021

Where
AL7 = year of work
H7 = frequency
W6 = column year
T7 = work cost value (spread value so total divided by spreadperiod)
S7 = smoothing period

=IF(AND(W$6>($AL7-1),W$6<($AL7+$S7)),$T7,0)

Can anyone help me combine the two formulas?

 

[Gerald Higgins]

Please can you give us SEVERAL DIFFERENT examples of possible data, showing what the results should be from the combined formula.

Please show AT LEAST TWO DIFFERENT examples where the first version of the formula applies, and AT LEAST TWO MORE DIFFERENT examples where the second version of the formula applies.

 

[eddster]

Hi Gerald

Please see below hope this helps

I am aware there maybe problems where the smoothing/frequency values may cause an overlap

Desired Output from formula
	G	H	O	P	Q	S	T	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG		AL
6	ITEM	frequency (years)	Quantity	rate	cost	spread	spread cost	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029		Year of proposed work
7	X	1	1	10	10	1	10	10	10	10	10	10	10	10	10	10	10	10		2019
8	Y	5	2	50	100	1	100		100					100						2020
9	Z	2	3	40	120	1	120			120		120		120		120		120		2021
10	A	3	2	15	30	3	10				10	10	10	10	10	10	10	10		2022
11	AB	5	5	100	500	3	166.6666667					166.66667	166.66667	166.66667			166.66667	166.66667		2023
12	C	6	1	1000	1000	5	200						200	200	200	200	200	200		2024
Formula 1	cellW7 =IF($AL7="",0,IF(AND($H7="N/A",$AL7=W$6),$T7,IFERROR(IF($AL7>W$6,0,((MOD(W$6-$AL7,$H7)=0)*$T7)),0)))
This formula applies the recurring frequency but not the smoothing
	G	H	O	P	Q	S	T	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG		AL
6	ITEM	frequency (years)	Quantity	rate	cost	spread	spread cost	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029		Year of proposed work
7	X	1	1	10	10	1	10	10	10	10	10	10	10	10	10	10	10	10		2019
8	Y	5	2	50	100	1	100	0	100	0	0	0	0	100	0	0	0	0		2020
9	Z	2	3	40	120	1	120	0	0	120	0	120	0	120	0	120	0	120		2021
10	A	3	2	15	30	3	10	0	0	0	10	0	0	10	0	0	10	0		2022
11	AB	5	5	100	500	3	166.6666667	0	0	0	0	166.66667	0	0	0	0	166.66667	0		2023
12	C	6	1	1000	1000	5	200	0	0	0	0	0	200	0	0	0	0	0		2024
Formula 2	CellW7 =IF(AND(W$6>($AL7-1),W$6<($AL7+$S7)),$T7,0)
This formula applies the smoothing but not the recurring frequency works
This formula applies the smoothing but not the recurring frequency	G	H	O	P	Q	S	T	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG		AL
6	ITEM	frequency (years)	Quantity	rate	cost	spread	spread cost	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029		Year of proposed work
7	X	1	1	10	10	1	10	10	0	0	0	0	0	0	0	0	0	0		2019
8	Y	5	2	50	100	1	100	0	100	0	0	0	0	0	0	0	0	0		2020
9	Z	2	3	40	120	1	120	0	0	120	0	0	0	0	0	0	0	0		2021
10	A	3	2	15	30	3	10	0	0	0	10	10	10	0	0	0	0	0		2022
11	AB	5	5	100	500	3	166.6666667	0	0	0	0	166.66667	166.66667	166.66667	0	0	0	0		2023
12	C	6	1	1000	1000	5	200	0	0	0	0	0	200	200	200	200	200	0		2024

<colgroup><col width="64" style="width: 48pt;" span="2"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="64" style="width: 48pt;" span="2"> <col width="122" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4461;"> <col width="64" style="width: 48pt;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;" span="12"> <col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4717;"> <tbody> </tbody>

 

[Gerald Higgins]

Try this in W7 . . . .

=IF(W$6>=$AL7,IF($S7=$H7,$T7,IF(MOD(W$6-$AL7,$H7)<$S7,$T7,0)),0)

This appears to deliver the desired results, EXCEPT it returns a 0 for item C in 2029, but based on your logic I THINK that that is actually correct.

 

[eddster]

Excellent, thank you Gerald this seems to be doing just the job. Thanks again