Need help with this really long IF formula looking at 2 values

[manzier]

Hi all,

I'm making a calculator and will need to display a specific value based on the returned score. However, there are two values that will need to be taken into account in determining what will be displayed.

2 values:
- Score (C18)
- Count (D21)

The Score will be whatever the calculation returns
The Count will be entered in manually

The IF formula will need to return a value of either "High" "Med" "Low" or "OK"


Here's what the formula will need to do:

1) If Count = 1-25, then if Score > 65, "High", Score < 65, "Med", Score < 55 "Low", Score < 46, "OK"

2) If Count = 26-250, then if Score > 53, "High", Score < 53, "Med", Score < 45, "Low", Score < 36, "OK"

3) If Count = 251-1000, then if Score > 45, "High", Score < 45, "Med", Score < 38, "Low", Score < 31, "OK"

4) If Count = 1001-2500, then if Score > 38, "High", Score < 38, "Med", Score < 31, "Low", Score < 26, "OK"

5) If Count = >2500, then if Score > 30, "High", Score < 30, "Med", Score < 25, "Low", Score < 21, "OK"



If this is possible, and you can provide assistance, I'll be very appreciative

Thanks!

 

[NeonRedSharpie]

This can be done really easily in VBA with a case statement and then converted into a User Define Function:

Function CALCFUNC(score As Variant, count As Variant) As Variant

    Select Case count
        Case Is < 25
            Select Case score.Value
                Case Is >= 65
                    CALCFUNC = "High"
                    Exit Function
                Case Is < 65
                    CALCFUNC = "Med"
                    Exit Function
                Case Is < 55
                    CALCFUNC = "Low"
                    Exit Function
                Case Is < 46
                    CALCFUNC = "OK"
                    Exit Function
                Case Else
                    CALCFUNC = "Invalid Entry"
                    Exit Function
            End Select
        Case Is < 250
            Select Case score.Value
                Case Is >= 53
                    CALCFUNC = "High"
                    Exit Function
                Case Is < 53
                    CALCFUNC = "Med"
                    Exit Function
                Case Is < 45
                    CALCFUNC = "Low"
                    Exit Function
                Case Is < 36
                    CALCFUNC = "OK"
                    Exit Function
                Case Else
                    CALCFUNC = "Invalid Entry"
                    Exit Function
            End Select
        Case Is < 2500
            Select Case score.Value
                Case Is >= 45
                    CALCFUNC = "High"
                    Exit Function
                Case Is < 45
                    CALCFUNC = "Med"
                    Exit Function
                Case Is < 38
                    CALCFUNC = "Low"
                    Exit Function
                Case Is < 31
                    CALCFUNC = "OK"
                    Exit Function
                Case Else
                    CALCFUNC = "Invalid Entry"
                    Exit Function
            End Select
        Case Is >= 2500
            Select Case score.Value
                Case Is >= 30
                    CALCFUNC = "High"
                    Exit Function
                Case Is < 30
                    CALCFUNC = "Med"
                    Exit Function
                Case Is < 25
                    CALCFUNC = "Low"
                    Exit Function
                Case Is < 21
                    CALCFUNC = "OK"
                    Exit Function
                Case Else
                    CALCFUNC = "Invalid Entry"
                    Exit Function
            End Select
        Case Else
            CALCFUNC = "Invalid Entry"
            Exit Function
    End Select
    
End Function

 

[Misca]

Try the following:

Excel Workbook
	A	B	C	D	E	F	G
1	Count	Count 1	Count 2	Count 3	Count 4	Count 5	Returns
2	1	0	0	0	0	0	OK
3	26	46	36	31	26	21	Low
4	251	55	45	38	31	25	Med
5	1001	65	53	45	38	30	High
6	2501
7
8		Count	Score	Answer
9		2600	47	High
10
Taul4

The English version of the formula in D9 would be:

=INDEX($G$2:$G$5,MATCH($C$9,OFFSET($A$2:$A$5,,MATCH($B$9,$A$2:$A$6))))

 

[manzier]

Try the following:
Taul4

*	A	B	C	D	E	F	G
1	Count	Count 1	Count 2	Count 3	Count 4	Count 5	Returns
2	1	0	0	0	0	0	OK
3	26	46	36	31	26	21	Low
4	251	55	45	38	31	25	Med
5	1001	65	53	45	38	30	High
6	2501	*	*	*	*	*	*
7	*	*	*	*	*	*	*
8	*	Count	Score	Answer	*	*	*
9	*	2600	47	High	*	*	*
10	*	*	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D9 =INDEKSI($G$2:$G$5;VASTINE($C$9;SIIRTYMÄ($A$2:$A$5;;VASTINE($B$9;$A$2:$A$6)))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

The English version of the formula in D9 would be:

=INDEX($G$2:$G$5,MATCH($C$9,OFFSET($A$2:$A$5,,MATCH($B$9,$A$2:$A$6))))

This is is amazing. Thank you!

I never would've thought to go this route

 

[manzier]

This can be done really easily in VBA with a case statement and then converted into a User Define Function:

Function CALCFUNC(score As Variant, count As Variant) As Variant

    Select Case count
        Case Is < 25
            Select Case score.Value
                Case Is >= 65
                    CALCFUNC = "High"
                    Exit Function
                Case Is < 65
                    CALCFUNC = "Med"
                    Exit Function
                Case Is < 55
                    CALCFUNC = "Low"
                    Exit Function
                Case Is < 46
                    CALCFUNC = "OK"
                    Exit Function
                Case Else
                    CALCFUNC = "Invalid Entry"
                    Exit Function
            End Select
        Case Is < 250
            Select Case score.Value
                Case Is >= 53
                    CALCFUNC = "High"
                    Exit Function
                Case Is < 53
                    CALCFUNC = "Med"
                    Exit Function
                Case Is < 45
                    CALCFUNC = "Low"
                    Exit Function
                Case Is < 36
                    CALCFUNC = "OK"
                    Exit Function
                Case Else
                    CALCFUNC = "Invalid Entry"
                    Exit Function
            End Select
        Case Is < 2500
            Select Case score.Value
                Case Is >= 45
                    CALCFUNC = "High"
                    Exit Function
                Case Is < 45
                    CALCFUNC = "Med"
                    Exit Function
                Case Is < 38
                    CALCFUNC = "Low"
                    Exit Function
                Case Is < 31
                    CALCFUNC = "OK"
                    Exit Function
                Case Else
                    CALCFUNC = "Invalid Entry"
                    Exit Function
            End Select
        Case Is >= 2500
            Select Case score.Value
                Case Is >= 30
                    CALCFUNC = "High"
                    Exit Function
                Case Is < 30
                    CALCFUNC = "Med"
                    Exit Function
                Case Is < 25
                    CALCFUNC = "Low"
                    Exit Function
                Case Is < 21
                    CALCFUNC = "OK"
                    Exit Function
                Case Else
                    CALCFUNC = "Invalid Entry"
                    Exit Function
            End Select
        Case Else
            CALCFUNC = "Invalid Entry"
            Exit Function
    End Select
    
End Function

Wow thank you! This is neat stuff