Values based on text

[jp39208]

I have created the following drop down list

INVESTIGATION OWN VEHICLE
NO VEHICLE AVAILABLE INVESTIGATION
RIDE ALONG
VEHICLE NOT FEASIBLE INVESTIGATION
INVESTIGATION OWN VEHICLE (HIGH)
NO VEHICLE AVAILABLE INVESTIGATION (HIGH)
RIDE ALONG (HIGH)
VEHICLE NOT FEASIBLE INVESTIGATION (HIGH)

the "HIGH" designation refers to a high cost area and a high cost area pays back higher reimbursements on expenses:
"HIGH" pays 41
other reimbursements pay 36 (i.e., just "VEHICLE NOT FEASIBLE" etc..)

so:
I have the drop down in say A1
B2, B3, and B4 are Breakfast, lunch and dinner respectively
B5 is summed and already has a high limit of 41

What I want to do is have excel (07') recognized the text in cell A1 as other than "HIGH" and only permit cell B5 to have a permitted value that cannot exceed 36 but if cell A1 does contain "HIGH" in the text permit it to go to 41

Thank you for any help that can be given!!!

[tigeravatar]

jp39208,

Give this formula a try in cell B5:
=MIN(IF(COUNTIF(A1,"*high*"),41,36),SUM(B2:B4))

[jp39208]

It works BEAUTIFULLY!!!!

I have multiple sheets and this is what I worked into it to provide a filler rather than zero

=IF(SUM('BACK TEST'!E6:G6)>0,(MIN(IF(COUNTIF('BACK TEST'!B6,"*high*"),41,36),SUM('BACK TEST'!E6:G6))),"-")