IF Statements Trouble

[scotty1978]

Trying to create an IF formula which is a little tricky, hoping to get some help.

Here are conditions:

IF B2*1 is less than or equal to D2 , then show Text D2. If B2*1 is greater than or equal to D2, then show Text D3. If B2*1 is greater than or equal to B4, then show Text D4.

The formula will then repeat itself below B2*2, B2*3, B2*4 in the corresponding cells below down the line. I'm not too concerned about that. If there is a formula that works for the example above, I can make those adjustments.

Thanks in advance

 

[Skrej]

Is this what you're looking for?

=IF(B2*1>=B4,D4,IF(B2*1<=D2,D2,IF(B2*1>=D2,D3)))

 

[StephenCrump]

If B2*1 is greater than or equal to D2, then show Text D3. If B2*1 is greater than or equal to B4, then show Text D4.

What happens when you increase the multiple to 20, and B2 * 20 is greater than D2 and greater than B4?

Or did you perhaps mean: If B2*1 is greater than or equal to D3, then show Text D4?

 

[scotty1978]

Is this what you're looking for?

=IF(B2*1>=B4,D4,IF(B2*1<=D2,D2,IF(B2*1>=D2,D3)))

Thank you! I realized after the fact that I did a really poor job in explaining. I tried to simplify things from worksheet I'm using and realized I provided the wrong info. I have attached a new image which has the exact placement on my worksheet and a better description (I hope) . Any help would be appreciate. Also, below Stephen raises a great point that I experienced when playing around with your formula. Once the numbers increase, for example Y1*20, Y*21, etc... #SPILL error occurs. Any solution to this would be greatly appreciated. This thing is driving me crazy and appreciate all the help.

Is this what you're looking for?

=IF(B2*1>=B4,D4,IF(B2*1<=D2,D2,IF(B2*1>=D2,D3)))

 

[scotty1978]

What happens when you increase the multiple to 20, and B2 * 20 is greater than D2 and greater than B4?

Or did you perhaps mean: If B2*1 is greater than or equal to D3, then show Text D4?

Thank you for your feedback. You raised an excellent point and I tried to do a better job explaining my scenario in the post above in response to Skrej. The spill error will still occur in this scenario and hoping for a solution. Thanks again!

 

[StephenCrump]

So perhaps this is closer?

	S	T	U	V	W	X	Y
1	1	100				DR	100
2	2	200				Ded	1000
3	3	300				OP	2000
4	4	400				PC	400
5	5	500
6	6	600
7	7	700
8	8	800
9	9	900
10	10	1000
11	11	400
12	12	400
13	13	400
14	14	400
15	15	400
16	16	400
17	17	400
18	18	400
19	19	400
20	20	0
21	21	0
22	22	0
23	23	0
24	24	0
25	25	0
26	26	0
27	27	0
28	28	0
29	29	0
30	30	0
31
Sheet1

Cell Formulas
Range	Formula
S1:S30	S1	=SEQUENCE(30)
T1:T30	T1	=LET(n,S1#,IF(DR*n<=Ded,DR*n,IF(DR*n>=OP,0,PC)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Ded	=Sheet1!$Y$2	T1
DR	=Sheet1!$Y$1	T1
OP	=Sheet1!$Y$3	T1
PC	=Sheet1!$Y$4	T1

 

[scotty1978]

This looks perfect, but can't seem to get it to work on my end. I'm good with the Sequence Range in S1. I Entered your formula in T1. When I plug in T1 gets a #SPILL and the others gets a #NAME. I think the issue is with the Named Ranges. Maybe not inputing them correctly? How are the named ranges supposed to be input on the sheets? I've never used the Let Fx so maybe that's why I'm having trouble. This great though, thank you....any tips on getting this to work would be appreciated.

 

[StephenCrump]

Have you defined the names?

If you don't want to use range names, try:

=LET(n,S1#,IF(Y1*n<=Y2,Y1*n,IF(Y1*n>=Y3,0,Y4)))
or
=LET(n,SEQUENCE(30),IF(Y1*n<=Y2,Y1*n,IF(Y1*n>=Y3,0,Y4)))

The formula should be entered only in T1. I suspect you've copied down to T2:T30, which is why the #SPILL! error in T1.

 

[Dave Patton]

Does Stephen Crump's post # 6 give you the answer?

What result are you looking for?

​

 

[scotty1978]

Ok! This worked. The only problem now is that if Y1*1 or Y1*2, etc, is less than Y2, it needs to only show Y1. It seems like it's adding the amounts of Y1 together until it gets passed the amount in Y2. Everything else is working beautifully.