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

manzier

Board Regular
Joined
Jul 21, 2014
Messages
96
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!
 
Last edited:

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
This can be done really easily in VBA with a case statement and then converted into a User Define Function:

Code:
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

Well-known Member
Joined
Aug 12, 2009
Messages
1,667
Try the following:
Excel Workbook
ABCDEFG
1CountCount 1Count 2Count 3Count 4Count 5Returns
2100000OK
3264636312621Low
42515545383125Med
510016553453830High
62501
7
8CountScoreAnswer
9260047High
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

Board Regular
Joined
Jul 21, 2014
Messages
96
Try the following:
Taul4

*ABCDEFG
1CountCount 1Count 2Count 3Count 4Count 5Returns
2100000OK
3264636312621Low
42515545383125Med
510016553453830High
62501******
7*******
8*CountScoreAnswer***
9*260047High***
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
CellFormula
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

Board Regular
Joined
Jul 21, 2014
Messages
96
This can be done really easily in VBA with a case statement and then converted into a User Define Function:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,608
Members
414,081
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top