MS Excel IF statement with multiple conditions

**roma** · 05-04-22, 01:12 AM

Hi can some help me create formula for Excel spreadsheet so if
Input value is 15
Output value ?

Range Value
0... 9 = 0
10 19 = 1
20 29 = 2
30 39 = 3
40 49 = 4
50 59 = 5
60 69 = 6
70 79 = 7
80 89 = 8
90 99 = 9

Thanks

**lsemmens** · 05-04-22, 11:20 AM

You question makes no sense? Are you trying to say if range is between 10 and 19 then the response would be 1? i.e. if 15 > 10 and 15 <20 then result = 1

**roma** · 05-04-22, 01:23 PM

Hi Isemmes,

Using the data from the Value ( 0 to 99 ).
Correct Output Value is outputted as a result of the Input Value

If the Input Value is 5 then the the Output Value is 0
If the Input Value is 21 then the the Output Value is 2
If the Input Value is 65 then the the Output Value is 6
If the Input Value is 93 then the the Output Value is 9
and so on.

This is a sample data of numbers what I need

I don't know how to upload sample xlxs file of what I need to achieve Sorry.

**jglnb** · 05-04-22, 05:51 PM

A little bit of cheating... however...

Range 1 Range 2 Value Helper Column Input 601
654 660 0 =+IF(AND((B5<=$G$4),($G$4<=C5)),0,"") Output =+SUM(E5:E14)
648 653.9 1 =+IF(AND((B6<=$G$4),($G$4<=C6)),1,"")
642 647.9 2 =+IF(AND((B7<=$G$4),($G$4<=C7)),2,"")
636 641.9 3 =+IF(AND((B8<=$G$4),($G$4<=C8)),3,"")
630 635.9 4 =+IF(AND((B9<=$G$4),($G$4<=C9)),4,"")
624 629.9 5 =+IF(AND((B10<=$G$4),($G$4<=C10)),5,"")
618 623.9 6 =+IF(AND((B11<=$G$4),($G$4<=C11)),6,"")
612 617.9 7 =+IF(AND((B12<=$G$4),($G$4<=C12)),7,"")
606 611.9 8 =+IF(AND((B13<=$G$4),($G$4<=C13)),8,"")
600 605.9 9 =+IF(AND((B14<=$G$4),($G$4<=C14)),9,"")

You might need to double the check the less than or equal to ensure correct results on the boundary !

**lsemmens** · 05-04-22, 06:21 PM

That might work, provided that Range1 And Range2 are constant. If either of those values changes then you may get multiple results in the Helper column which would then translate to the sum total of those values in the output. A might be better. I'm still attempting to understand exactly what it is the OP is trying to achieve. Is this an assignment? Or is there a real world need?