If I select a word from the drop-down menu, how to calculate selected cells with numbers

K0st4din

Active Member
Joined
Feb 8, 2012
Messages
391
Office Version
  1. 2013
  2. 2011
  3. 2010
  4. 2007
Platform
  1. Windows
Hello friends, I was looking for a solution on the Internet, but I could not find what I needed. I have a drop-down menu with 4 words (for example: Analgin, Aspirin, Tree, Flowers) in cell b1. When I choose a word, I want to take specific values from specific cells, for example from a1 x a3 + b3 / a5 - for Analgin. Or for Aspirin a1 * a3 + b4 / b2 - c2, etc. for the other selected words. The result is in cell b10, no matter what I choose. I will be grateful if anyone can help me.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
Can you post an example of the full data? Not enough detail or precision to understand what you need or figure it out. You can use XL2BB to post screenshots

As a guess, a mapping table, with lookup into B10 may be one solution.
 

K0st4din

Active Member
Joined
Feb 8, 2012
Messages
391
Office Version
  1. 2013
  2. 2011
  3. 2010
  4. 2007
Platform
  1. Windows
Hello,
this is just an example, but the idea in a sentence is that if a word is found in a given cell, it starts counting on the desired cells.
Book1
ABCDEFGHIJKLM
1AspirineAspirine
2Analgine
3543510Food bananas
4if Aspirine then in B6 = d3+f3 * e3Wood
5if Analgine then in B6 = d3+g3/h3
6if Food bananas in B6 = f3*g3+h3/b3
7ect…..
8
Sheet1
Cells with Data Validation
CellAllowCriteria
B1List=$M$1:$M$4
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
I'm afraid it's still not clear what the map is from the 4 words to the formula

What connects
Aspirine to 5, 4 and 3 and is that 5 + (4*3)?
Analgine to 5, 5, 10 and is that 5 + (5/10)?

Why does it switch from 4*3 to 5/10? Then Food bananas has a different constant (3*5 not 5) and scaling factor (10/B3, B3 is empty in above)
I can't tell what pattern creates the formula against the words I'm afraid to suggest a formula unless you have 4 distinct formulae for each word selection
 

K0st4din

Active Member
Joined
Feb 8, 2012
Messages
391
Office Version
  1. 2013
  2. 2011
  3. 2010
  4. 2007
Platform
  1. Windows

ADVERTISEMENT

Hi again, sorry for the late reply.
Here is the whole idea, as I did not make two more of the requirements for small children and babies, but for adults I did it and showed everything necessary with explanations.
And I remain available if there are still uncertainties.
I know how to calculate things, I just don't know how to combine and make a calculation according to the choice of drop-down menus
One of my words makes it an asterisk because he thinks it's a bad word, but it's just the word.



Book1
ABCDEFGHIJKLMN
1word1****croft-Gault formula:****croft-Gaultman
2word twoFor men: ClKp = ((140-year-old) * weight) / (72 * KrPl).Jelliffe formulaswoman
3word 3For women: ClKr = ((140-age) * weight) / (72 * KrPl)) * 0.85, where ClKr - assessment of creatinine clearance, ml / min weight - weight, kg KrPl - plasma creatinine, mg / dl .next 3kids
4word 4constantIn cases where creatinine is defined in mol / l, the indicator 0.8 is used in the formula instead of 72. This is because 1 mg / dL is 88.4 μmol / L.next 4babies
5140
6for word1I will write ageI will write weight kgI will write %
7if a man65x92/72 or 0.8x
8if a woman65x92/72 or 0.8xx0.85<<<--constant
9
10Jelliffe formulas:
11for word twoFor men: ClPr = (98-0.8 * (age-20)) / KrPl.
12For women: KlKr = (98-0.8 * (age-20)) / KrPl) * 0.90, where KlKr - assessment of creatinine clearance, ml / min / 1.73 sq. M KrPl - creatinine plasma mg / dl.
13
14I will write ageI will write weight kgconstantconstantconstantconstant
15if a man71x67980.8200.90
16if a woman64x88
17
18
19
20
21
22my idea****croft-Gault
23man
24
25AGE<---- I will write age
26weigh in kg<------I will write weight kg
27
28
29
30result
31
32
Sheet1
Cells with Data Validation
CellAllowCriteria
C22List=$M$1:$M$4
C23List=$N$1:$N$4
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
It's still not clear, but does this help?
Other.xlsm
ABCDEF
1
2KrPl12List of words
3This is a man
4This is a woman
5I am Batman
6
7Word1Age inputFormulaWeight input
8This is a man65364.570
9This is a woman65328.0560
10I am Batman65060
Main
Cell Formulas
RangeFormula
C8:C10C8=(--ISNUMBER(SEARCH(" man "," "&$A8&" "))+0.9*(--ISNUMBER(SEARCH(" woman "," "&$A8&" "))))*((98-0.8)*($B8-20))/KrPl


C2 is a named cell KrPl containing the constant value used in the formula in C8:C12
A8:A10 are driven by drop-down validation lists with formula:
Excel Formula:
 =$F$3:$F$5
 

K0st4din

Active Member
Joined
Feb 8, 2012
Messages
391
Office Version
  1. 2013
  2. 2011
  3. 2010
  4. 2007
Platform
  1. Windows
Hello
I think we are getting closer to what I am looking for. :)
Please see my idea from the table shown in my previous comment.
So, in D1 is the word, in d2 and d3 are the two formulas for men and women.
In c10 is the other word, in c11 and c12 are the other 2 formulas.
This so far is to help understand what the formulas are according to the words.
The words and whether he is a man, a woman, a child are in M and N, so I can make drop-down menus in c22 (words), c23 (man, woman, child), in c25 (I write the years), in c26 ( height in centimeters) and in c30 I should get the result according to the selected and written criteria above.
So that we can reach the end, I remain available!
Because in the case of each thing we have a series of lines - ie for women, for men and for the other two words (attention !!! which I did not add), because I still think that if we do it for these two, after we will easily add the other two.
 

Forum statistics

Threads
1,144,341
Messages
5,723,811
Members
422,518
Latest member
quack_quack

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