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

#### manzier

##### Board Regular
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:

### 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
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``````

• manzier

#### Misca

##### Well-known Member
Try the following:
Excel Workbook
ABCDEFG
1CountCount 1Count 2Count 3Count 4Count 5Returns
2100000OK
3264636312621Low
42515545383125Med
510016553453830High
62501
7
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

#### manzier

##### Board Regular
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>

 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

##### Board Regular
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 Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,146
Messages
5,835,664
Members
430,373
Latest member
swartzfeger ### 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.

### Which adblocker are you using?    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

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