If Statement

[FanofExcel18]

Need Help

I'm weighting hours.

Example: Work hours for the month is 150

1 = 120<
2 = 121-135
3 = 135-150
4 = 150>

 

[Fluff]

Something like
=LOOKUP(B1,{0;121;135;150},{1;2;3;4})

 

[Scott Huish]

Your ranges overlap.
Assuming you mean 120 or less, 121-135, 136-150, 151 and over

=MATCH(B1,{0,120,136,151},1)

or if you're dealing with fractional hours, perhaps:
=MATCH(B1,{0,120,135.01,150.01},1)

 

[FanofExcel18]

Your ranges overlap.
Assuming you mean 120 or less, 121-135, 136-150, 151 and over

=MATCH(B1,{0,120,136,151},1)

or if you're dealing with fractional hours, perhaps:
=MATCH(B1,{0,120,135.01,150.01},1)

The time could change per month. Originally I was trying to do something like:
=IF(I6 < hours!$c$10,1,if(i6=hours!$c$10,2,if(i6 > Hours!$C$10,3,4)))<hours!$c$10,1,if(i6=hours!$c$10,2,if(i6>

Hours!$C$10 = that months hours</hours!$c$10,1,if(i6=hours!$c$10,2,if(i6>

 

[Fluff]

Do these bands vary from month to month
120 or less, 121-135, 136-150, 151 and over

 

[FanofExcel18]

Do these bands vary from month to month
120 or less, 121-135, 136-150, 151 and over

They will by I can make those adjustments as time goes on.

 

[Fluff]

In that case have you tried either of the suggestions supplied?

 

[FanofExcel18]

In that case have you tried either of the suggestions supplied?

=LOOKUP(B1,{0;121;135;150},{1;2;3;4}) this works well but b1 needs to be <,=,> on a base number for that month.

 

[Fluff]

I do not understand what you are after.
Can you please post some data showing what you have & what you want.
There some some tools to help with that if you follow the link in my signature.

 

[FanofExcel18]

[TABLE="width: 294"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]Time[/TD]
[TD]Amount[/TD]
[TD] [/TD]
[TD]Hours[/TD]
[TD="align: right"]142[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD] 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]117[/TD]
[TD] 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]162[/TD]
[TD] 4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]142[/TD]
[TD] 3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]