Multiple arguments with multiple answers

[mactoolsix]

For cell M50, If B50>20 then M50 = 100 but if M49>1 then M49 * 1.5

If I then copy M50 to M51,M52, M53, M54 etc, once M50 has a number the following cells will multiply that previous cell value by 1.5.

If B50 is never >20,then all M values = 0
Once B50, or B51, B52, B53 etc >20, then corresponding M = 100
Then the next cell in column M would be 100 * 1.5

Hope that's clearer than mud!
Thanks, Mike

 

[jtakw]

Hi,

Not sure what you mean, yeah clear as mud, lol, maybe this?

Book1
	B	M
50	21	100
51	3	0
52	23	100
53	22	150
54	20	0
55	50	100
56	60	150
57	70	225
58	80	337.5
59	2	0
60	100	100
Sheet49

Cell Formulas
Range	Formula
M50	=IF(B50>20,IF(M49<>0,M49*1.5,100),0)

M50 formula copied down.

 

[mactoolsix]

Okay Works, except when B50 is not > 20 I get "FALSE"
Anyway of replacing the "FALSE" with zero or blank?

Thanks!!

 

[jtakw]

Don't know why you would get "FALSE", if B50 is not greater than 20, the formula will return 0.
How is the value in B50 or all the values in column B entered? Manually or by formula? If formula, please post the formula.

Book1
	B	M
50	19	0
51	3	0
52	23	100
53	22	150
54	20	0
55	50	100
56	60	150
57	70	225
58	80	337.5
59	2	0
60	100	100
Sheet49

Cell Formulas
Range	Formula
M50	=IF(B50>20,IF(M49<>0,M49*1.5,100),0)

 

[mactoolsix]

B50=IF(B49<FinalYr,B49+1," ")
When this returns less than 20, I get "FALSE"

 

[jtakw]

Please repost your formula putting a space before and after < or > symbols.

 

[mactoolsix]

Sorry about that . . .

=IF(B49 < FinalYr,B49+1," ")

If FinalYr is less than 20 I get "FALSE"

 

[jtakw]

Kinda difficult to diagnose the problem cause you've got other formulae going on here, but let's start by removing the space " " in your formula and replace it with a Blank "", then add an AND test to my formula:

Book1
	B	M	N	O
49	3
50	4	0		33
51	3	0
52	23	100
53	22	150
54	20	0
55	50	100
56	60	150
57	70	225
58	80	337.5
59	2	0
60	100	100
Sheet49

Cell Formulas
Range	Formula
B50	=IF(B49)
M50	=IF(AND(B50<>"",B50>20),IF(M49<>0,M49*1.5,100),0)

Named Ranges
Name	Refers To	Cells
FinalYr	=Sheet49!$O$50

 

[mactoolsix]

Okay, here's what I've got:

B50 = IF(B49 < FinalYr,B49+1,"") correctly returns 2058 (above I told you "20" trying to keep things simple)
FinalYr is 2086
B49 = 2057

K50 = IFERROR(F50+G50+I50+J50,0) correctly returns 0
L50 = IF(B50 < RetYr,L49*(1+Inflation),"") correctly returns ""
M50 = IF(AND(B50 < > "",B50 > RetYr-1),IF(M49 < > 1,M49*(1+Inflation),FirstYrAnnExp)) correctly returns 180,100
Inflation = .03
FirstYrAnnExp = 174,855


RetYr is 2057

O50 = K50-L50-M50-N50 Returns #VALUE !

 

[jtakw]

You're asking the SAME question here: https://www.mrexcel.com/forum/excel-questions/1042728-how-can-i-get-formula-ignore-entries.html

As suggested in your OTHER thread, do this:

O50 = K50-SUM(L50)-M50-N50

If N50 may also contain "", add SUM to it like SUM(N50) in the formula.