Help with "IF"

[garretttpe]

Hi All
Attached is a spread sheet and I want to be able to add or subtract numbers in a row if there is a I (Install) or R (removed) in the 1st cell. hard to explain and I hope seeing the spread sheet you get what I am trying to do. As of now the "weight" of the item I have to put a negative number for the row calculations to work and that is not right but the only way I know how.
thank you all very much for any help

test.xlsx
	A	B	C	D	E	F	G	H	I
8	8/26/2021	Data Transferred From Previous Chart C					10,282.0	231.47	2,380,020.41
9	8/26/2021	Life Raft & Vests	I	99.8	120.20	11,995.96	10,381.8	230.40	2,392,016.37
10	8/31/2021	Life Raft & Vests	R	-99.8	120.20	-11,995.96	10,282.0	231.47	2,380,020.41
11	9/12/2021	Martin Baker Seat Right 6	I	15.0	231.91	3,478.65	10,297.0	231.48	2,383,499.06
12	9/12/2021	Martin Baker Seat Right 7	I	15.0	242.00	3,630.00	10,312.0	231.49	2,387,129.06
13	9/12/2021	Martin Baker Seat Right 8	I	15.0	252.09	3,781.35	10,327.0	231.52	2,390,910.41
14	9/12/2021	Martin Baker Seat Right 9	I	15.0	262.18	3,932.70	10,342.0	231.56	2,394,843.11
15	9/12/2021	Martin Baker Seat Right 10	I	15.0	272.27	4,084.05	10,357.0	231.62	2,398,927.16
16	9/12/2021	Martin Baker Seat Right 11	I	15.0	282.36	4,235.40	10,372.0	231.70	2,403,162.56
17	9/12/2021	Martin Baker Seat left 16	I	15.0	246.78	3,701.70	10,387.0	231.72	2,406,864.26
18	9/12/2021	Martin Baker Seat left 17	I	15.0	266.87	4,003.05	10,402.0	231.77	2,410,867.31
19	9/12/2021	Martin Baker Seat left 18	I	15.0	286.96	4,304.40	10,417.0	231.85	2,415,171.71
20	9/12/2021	Martin Baker Seat left 19	I	15.0	307.05	4,605.75	10,432.0	231.96	2,419,777.46
21		Martin Baker Seat left 17	R	-15.0	266.87	-4,003.05	10,417.0	231.91	2,415,774.41
22		Martin Baker Seat left 18	R	-15.0	286.96	-4,304.40	10,402.0	231.83	2,411,470.01
23		Martin Baker Seat left 19	R	-15.0	307.05	-4,605.75	10,387.0	231.72	2,406,864.26
24		As you can see I have to make the "weight" a negative nuymber for the rest of the row to calculate and that is not correct, If there is a R in "C" then "F" should be subtracted from the previous entry "I" and opposite if there is an "I" in "C"
25
26
27
New Form C

Cell Formulas
Range	Formula
F9:F23	F9	=D9*E9
G9:G23	G9	=IF(D9=""," ",G8+D9)
H9:H23	H9	=IF(D9="","",I9/G9)
I9:I23	I9	=IF(D9="","",I8+(D9*E9))

 

[johnnyL]

Your problem should be easily solvable. Just need some clarification of what formulas you are looking for.

If column C = I ... what formula do you want and where do you want the formula?
If Column C = R ... what formula do you want and where do you want the formula?

 

[Peter_SSs]

Like this?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

21 09 15.xlsm
	C	D	E	F			I
8							2,380,020.41
9	I	99.8	120.2	11995.96			2,392,016.37
10	R	99.8	120.2	11995.96			2,380,020.41
11	I	15	231.91	3478.65			2,383,499.06
12	I	15	242	3630			2,387,129.06
13	I	15	252.09	3781.35			2,390,910.41
14	I	15	262.18	3932.7			2,394,843.11
15	I	15	272.27	4084.05			2,398,927.16
16	I	15	282.36	4235.4			2,403,162.56
17	I	15	246.78	3701.7			2,406,864.26
18	I	15	266.87	4003.05			2,410,867.31
19	I	15	286.96	4304.4			2,415,171.71
20	I	15	307.05	4605.75			2,419,777.46
21	R	15	266.87	4003.05			2,415,774.41
22	R	15	286.96	4304.4			2,411,470.01
23	R	15	307.05	4605.75			2,406,864.26
RI

Cell Formulas
Range	Formula
F9:F23	F9	=D9*E9
I9:I23	I9	=IF(D9="","",I8+IF(C9="R",-F9,F9))

 

[garretttpe]

thanks for the fast response, not really working 100 percent, I will try to explain in better detail
1. If C9 is I then F9 will be D9*E9, easy part
2. If C9 is R than F9 will be D9*E9. same as above.
3. If I9 is "I" then F9 needs to be added to I8
4. If I9 is "R" then F9 needs to be subtracted from I8
C= I or R, D=weight, E=ARM, F=moment, G=new weight, H=CG, I=moment

looking to be able to add a positive number to D and the formulas do the adding or subtracting depending if C has I ( install) or R ( remove).
hope this helps, As of now I have to make "D" a negative for I to subtract and that is not correct
thank you

 

[johnnyL]

How about:

Book2
	A	B	C	D	E	F	G	H	I	J
8	8/26/2021	Data Transferred From Previous Chart C					10282	231.47	2380020.41
9	8/26/2021	Life Raft & Vests	I	99.8	120.2	11995.96	10381.8	230.4048	2392016.37
10	8/31/2021	Life Raft & Vests	R	99.8	120.2	11995.96	10481.6	227.0665	2380020.41
11	9/12/2021	Martin Baker Seat Right 6	I	15	231.91	3478.65	10496.6	227.0734	2383499.06
12	9/12/2021	Martin Baker Seat Right 7	I	15	242	3630	10511.6	227.0947	2387129.06
13	9/12/2021	Martin Baker Seat Right 8	I	15	252.09	3781.35	10526.6	227.1304	2390910.41
14	9/12/2021	Martin Baker Seat Right 9	I	15	262.18	3932.7	10541.6	227.1802	2394843.11
15	9/12/2021	Martin Baker Seat Right 10	I	15	272.27	4084.05	10556.6	227.2443	2398927.16
16	9/12/2021	Martin Baker Seat Right 11	I	15	282.36	4235.4	10571.6	227.3225	2403162.56
17	9/12/2021	Martin Baker Seat left 16	I	15	246.78	3701.7	10586.6	227.3501	2406864.26
18	9/12/2021	Martin Baker Seat left 17	I	15	266.87	4003.05	10601.6	227.406	2410867.31
19	9/12/2021	Martin Baker Seat left 18	I	15	286.96	4304.4	10616.6	227.4901	2415171.71
20	9/12/2021	Martin Baker Seat left 19	I	15	307.05	4605.75	10631.6	227.6024	2419777.46
21		Martin Baker Seat left 17	R	15	266.87	4003.05	10646.6	226.9057	2415774.41
22		Martin Baker Seat left 18	R	15	286.96	4304.4	10661.6	226.1828	2411470.01
23		Martin Baker Seat left 19	R	15	307.05	4605.75	10676.6	225.4336	2406864.26
24		As you can see I have to make the "weight" a negative nuymber for the rest of the row to calculate and that is not correct, If there is a R in "C" then "F" should be subtracted from the previous entry "I" and opposite if there is an "I" in "C"
25
26
27
Sheet1

Cell Formulas
Range	Formula
F9:F23	F9	=D9*E9
G9:G23	G9	=IF(D9=""," ",G8+D9)
H9:H23	H9	=IF(D9="","",I9/G9)
I9:I23	I9	= IF(C9="I",I8+F9,I8-F9)

 

[garretttpe]

thanks
not working, if there is a "R" in row C21 then "F" needs to be subtracted from the I20 with result in I21, does this make sense
thank you all for your patience

 

[johnnyL]

thanks
not working, if there is a "R" in row C21 then "F" needs to be subtracted from the I20 with result in I21, does this make sense

Not at all, because that is exactly what my last post does.

 

[Peter_SSs]

thanks for the fast response, not really working 100 percent,

Can you identify which is the first cell in my mini-sheet in post #3 is incorrect and what the correct value in that cell should be?

I will try to explain in better detail

3. If I9 is "I" then F9 needs to be added to I8
4. If I9 is "R" then F9 needs to be subtracted from I8

Those make no sense at all to me since column I contains numbers, not R or I

 

[johnnyL]

Can you identify which is the first cell in my mini-sheet in post #3 is incorrect and what the correct value in that cell should be?

Those make no sense at all to me since column I contains numbers, not R or C

@Peter_SSs actually it's R or I.

Upon further review, your code, as well as mine, resulted in the same results.

Not sure what @garretttpe is looking for, both of our formulas give what was asked for.

 

[Peter_SSs]

@Peter_SSs actually it's R or I.

Thanks for picking up that typo. I've fixed it in my post so that it makes sense there now.

Upon further review, your code, as well as mine, resulted in the same results.

Yes, I couldn't see that you were suggesting anything different to me (except not checking if col D was empty )