Calculation if a cell contains a word

J

James

I've exported data from a database and then performed a subtotal/count. I
need to multiply the count of the different criteria by three different
values.
If the Cell contains the word "High" I need to multiply the count by .85 if
"Low" by .45 and if "Medium" by .60

Area one Non Accommodation High Large 14
Area sixteen Non Accommodation High Small 7
Area three Accommodation Medium Small 6
Area one Accommodation Low Small 2
Area Five Non Accommodation Low Large 10
Area six Non Accommodation Medium Large 9

Cell A2 contains Area name and type and B2 contains the count.

Any help gratefully received.
 
D

David Biddulph

=IF(ISNUMBER(SEARCH("High",A2)),B2*.85,IF(ISNUMBER(SEARCH("Medium",A2)),B2*.60,IF(ISNUMBER(SEARCH("Low",A2)),B2*.45,"answer
unspecified")))
 
J

Jarek Kujawa

=NOT(ISERROR(FIND("High",A1,1)))*.85+NOT(ISERROR(FIND("Medium",A1,1)))
*.6+NOT(ISERROR(FIND("Low",A1,1)))*.45
 
J

Jacob Skaria

Try
=B1*IF(ISNUMBER(SEARCH("low",A1)),0.45,
IF(ISNUMBER(SEARCH("medium",A1)),0.6,
IF(ISNUMBER(SEARCH("high",A1)),0.85,1)))

If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional Format if cell contains a word 12
Changing a Picture or Object if a Cell equals a certain value 5
Count the words in a cell 8
Function to determine if a cell contains a formula 5
copy-pasting a cell and finding lows and highs 1
TRIM IF CELL CONTAINS ONE WORD, ABBREVIATE IF IT CONTAINS MULTIPLE WORDS 1
cell B1 contains a word listed in cell A1 2
Excel If a Cell Contains a Certain Text (From Another Table) 0

Top