combining multiple formulas into one if statement

[pile-it Mark]

Hello!

i am back playing with a status sheet again. had to start a new plane. i am trying to make it less error prone. in the Next Due Column H



Hours =f16+K16
Months =EOMONTH(j16,f16)
Cycles =l16+f16
Days =(J16+f16)
Years =EOMONTH(J16,F16*12)
Ldgs =L16+F16
AC Hours =IF(G131="AC Hours",F131+C$13,"b")

these formulas all work individually, they were taken from cells that i have been testing. but i would like to not have to insert and check 500 lines multiple times.

one formula in H that covers the condition in G.
that shows the calculation combining frequency in F with start in J (date), K (Hours), L (Cycles/Landings) A/C Hobbs in C$13



this works on the first two lines that are "hours" and "months" it does not like Ldgs or years

=IF(G17="Hours",K17+F17,IF(G17="Months",EOMONTH(J17,F17),IF9(G17="Ldgs",L17+F17,IF(G17="Years",EOMONTH(J17,F17*12),IF(G17="Days",J17+F17,IF(G17="AC Hours","fix ME"))))))

just realized i did not include Cycles in the if statement. Ldgs are airframe, Cycles are Engine. not always the same

any thoughts would be appreciated. i actually have the formulas in place now, but the sheet is available to others and if day gets switched to month in a revision,not everyone knows the formula needs changed.

Thanks for looking.
Mark

 

[SteveO59L]

try posting a sample of your data, and what you would like to do with it

 

[pile-it Mark]

Excel 2010
	E	F	G	H	I	J	K	L	M
16	SN or other info	FREQUENCY		NEXT DUE	REMAINING	complied with
17				#REF!		Date	Hour	Cycle	REMAINING
18		300	Hours	9517.1	299.4	7/5/2017	9217.1	12540	299
19		12	Months	31-Jul-18	137	7/5/2017	9217.1	12540	137
20		600	Hours	9817.1	599.4	7/5/2017	9217.1	12540	599
21		24	Months	31-Jul-19	502	7/5/2017	9217.1	12540	502
22		1200	Hours	10417.1	1199.4	7/5/2017	9217.1	12540	1199
23		48	Months	31-Jul-21	1233	7/5/2017	9217.1	12540	1233
24		2400	Hours	11617.1	2399.4	7/5/2017	9217.1	12540	2399
25		96	Months	31-Jul-25	2694	7/5/2017	9217.1	12540	2694
26		3000	Ldgs	6507.0	-6033	5/5/2013	4309.1	3507	-6033
27		12	Years	31-May-25	2633	5/5/2013	4309.1	3507	2633
28		6,000	Ldgs	9507.0	-3033	5/5/2013	4309.1	3507	-3033
29		12,000	Hours	20800.0	11582.3	5/5/2013	8800.0	3507	11582
TBO r2

 

[pile-it Mark]

Excel 2010
	E	F	G	H	I	J	K	L	M
16	SN or other info	FREQUENCY		NEXT DUE	REMAINING	complied with
17				#REF!		Date	Hour	Cycle	REMAINING
18		300	Hours	9517.1	299.4	7/5/2017	9217.1	12540	299
19		12	Months	31-Jul-18	137	7/5/2017	9217.1	12540	137
20		600	Hours	9817.1	599.4	7/5/2017	9217.1	12540	599
21		24	Months	31-Jul-19	502	7/5/2017	9217.1	12540	502
22		1200	Hours	10417.1	1199.4	7/5/2017	9217.1	12540	1199
23		48	Months	31-Jul-21	1233	7/5/2017	9217.1	12540	1233
24		2400	Hours	11617.1	2399.4	7/5/2017	9217.1	12540	2399
25		96	Months	31-Jul-25	2694	7/5/2017	9217.1	12540	2694
26		3000	Ldgs	6507.0	-6033	5/5/2013	4309.1	3507	-6033
27		12	Years	31-May-25	2633	5/5/2013	4309.1	3507	2633
28		6,000	Ldgs	9507.0	-3033	5/5/2013	4309.1	3507	-3033
29		12,000	Hours	20800.0	11582.3	5/5/2013	8800.0	3507	11582
TBO r2

Cell Formulas
Range	Formula
J19	=J18
J21	=J20
J23	=J22
J25	=J24
K19	=K18
K21	=K20
K23	=K22
K25	=K24
L19	=L18
L21	=L20
L23	=L22
L25	=L24
H17	=IF(G17="Hours",K17+F17,IF(G17="Months",EOMONTH(J17,F17),IF9(G17="Ldgs",L17+F17,IF(G17="Years",EOMONTH(J17,F17*12),IF(G17="Days",J17+F17,IF(G17="AC Hours","fix ME"))))))
H18	=IF(G18="Hours",K18+F18,IF(G18="Months",EOMONTH(J18,F18),IF10(G18="Ldgs",L18+F18,IF(G18="Years",EOMONTH(J18,F18*12),IF(G18="Days",J19+F18,IF(G18="AC Hours","fix ME"))))))
H19	=IF(G19="Hours",K19+F19,IF(G19="Months",EOMONTH(J19,F19),IF11(G19="Ldgs",L19+F19,IF(G19="Years",EOMONTH(J19,F19*12),IF(G19="Days",J20+F19,IF(G19="AC Hours","fix ME"))))))
H20	=K20+F20
H21	=IF(G21="Hours",K21+F21,IF(G21="Months",EOMONTH(J21,F21),IF13(G21="Ldgs",L21+F21,IF(G21="Years",EOMONTH(J21,F21*12),IF(G21="Days",J22+F21,IF(G21="AC Hours","fix ME"))))))
H22	=K22+F22
H23	=EOMONTH(J23,F23)
H24	=IF(G24="Hours",K24+F24,IF(G24="Months",EOMONTH(J24,F24),IF16(G24="Ldgs",L24+F24,IF(G24="Years",EOMONTH(J24,F24*12),IF(G24="Days",J25+F24,IF(G24="AC Hours","fix ME"))))))
H25	=EOMONTH(J25,F25)
H26	=L26+F26
H27	=EOMONTH(J27,F27*12)
H28	=L28+F28
H29	=K29+F29
I18	=H18-C$7
I19	=H19-C$2
I20	=H20-C$7
I21	=H21-C$2
I22	=H22-C$7
I23	=H23-C$2
I24	=H24-C$7
I25	=H25-C$2
I26	=H26-C$8
I27	=H27-C$2
I28	=H28-C$8
I29	=H29-C$7
M18	=I18
M19	=I19
M20	=I20
M21	=I21
M22	=I22
M23	=I23
M24	=I24
M25	=I25
M26	=I26
M27	=I27
M28	=I28
M29	=I29

i would like column H to have one formula that uses column G to decide which of the seven current formulas to use.

this is a test area of the working formulas

they have to be put into column H individually and manually checked the make sure they match the frequency of operations

Hours
Months
Cycles
Days
Years
AC Hours
Ldgs

Excel 2010
	L	M
2	Hours	9517.1
3	Months	31-Jul-18
4	Cycles	6507.0
5	Days	1-Dec-11
6	Years	30-Apr-87
7	AC Hours	822.2
8	Ldgs	6507.0
TBO r2

Cell Formulas
Range	Formula
M2	=F18+K18
M3	=EOMONTH(J19,F19)
M4	=L26+F26
M5	=(F40+J40)
M6	=EOMONTH(H27,J27+(F27*12))
M7	=IF(G131="AC Hours",C$13+F131)
M8	=L26+F26

 

[pile-it Mark]

trying to use the word in G to set the formula in H

Excel 2010
	L	M
1	if G=	Use this Formula
2	Hours	9517.1
3	Months	31-Jul-42
4	Cycles	12840.0
5	Days	1-May-18
6	Years	31-Oct-02
7	AC Hours
8	Ldgs	12840.0
9	#REF!
TBO r2

Cell Formulas
Range	Formula
M2	=F18+K18
M3	=EOMONTH(J18,F18)
M4	=L18+F18
M5	=(F18+J18)
M6	=EOMONTH(H18,J18+(F18*12))
M7	=IF(G18="AC Hours",C$13+F18," ")
M8	=L18+F18
L9	=IF(G17="Hours",K17+F17,IF(G17="Months",EOMONTH(J17,F17),IE9(G17+J4,IF(G17="Years",EOMONTH(J17,F17*12),IF(G17="Days",J17+F17,IF(G17="AC Hours","fix ME"))))))

this if statement returns errors

=IF(G18="Hours",K18+F18,IF(G18="Months",EOMONTH(J18,F18),If(G18+J4,IF(G18="Years",EOMONTH(J18,F18*12),IF(G18="Days",J18+F18,IF(G18="AC Hours","fix ME"))))))

 

[pile-it Mark]

found a spelling problem

=IF(G18="Hours",K18+F18,IF(G18="Months",EOMONTH(J18,F18),IF(G18="Ldgs",L18+F18,IF(G18="Years",EOMONTH(J18,F18*12),IF(G18="Days",J19+F18,IF(G18="AC Hours","fix ME"))))))


this seems to work if it is copied and special pasted in H as a formula only.

still need to add cycles and correct the AC Hours answer to match the formula

 

[pile-it Mark]

fixed a number error. in the "days" statement

=IF(G24="Hours",M24+F24,IF(G24="Months",EOMONTH(L24,F24),IF(G24="Ldgs",N24+F24,IF(G24="Cycles",N24+F24,IF(G24="Years",EOMONTH(L24,F24*12),IF(G24="Days",L24+F24,IF(G24="AC Hours",IF(G24="AC Hours",C$13+F24))))))))