If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculation if a cell contains a word
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. |
#2
|
|||
|
|||
Calculation if a cell contains a word
=IF(ISNUMBER(SEARCH("High",A2)),B2*.85,IF(ISNUMBER (SEARCH("Medium",A2)),B2*.60,IF(ISNUMBER(SEARCH("L ow",A2)),B2*.45,"answer
unspecified"))) -- David Biddulph "James" wrote in message ... 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. |
#3
|
|||
|
|||
Calculation if a cell contains a word
=NOT(ISERROR(FIND("High",A1,1)))*.85+NOT(ISERROR(F IND("Medium",A1,1)))
*.6+NOT(ISERROR(FIND("Low",A1,1)))*.45 On 20 Lis, 13:37, James wrote: 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. |
#4
|
|||
|
|||
Calculation if a cell contains a word
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 --------------- Jacob Skaria "James" wrote: 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. |
#5
|
|||
|
|||
Calculation if a cell contains a word
Thank you all for your replies this is exactly what i was looking for.
James :-) "James" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|