numbers
Newbie here!
I'm trying to find a function so when I enter a value in one cell (A1) it gives me a predetermined word in another cell (A2). The values are ranges (0-19, 20-39, 40-59, 60-79 & 80<) in another table (A1-B1 to A5-B5) and each has a different word associated to them (C1-5).
Can anyone help?
Many thanks in advance!
You need to use the $ char e.g. like this: =$D$3
You can do this with the IF function. The syntax is…
┌── IF function returns one of two values when upon an expression's evaluation.
│
│ ┌── string or calculation for cell to show upon TRUE/FALSE
│ │ strings should be inside double quotes "like this"
│ ┌────┴─────────┐
IF(if-expression,if-true,if-false)
└─────┬─────┘
└── the expression to evaluate
for example: to test if A1-A2 is negative, you can use (A1-A2)<0
the result must be a boolean
This results in the following calculation:
IF(Data::A1<0,"Negative","Positive")
Result:
An example for comparing a calculation in the if-expression:
┌── from table "Data" do A1-B1
│
│ ┌── is the result less than zero (i.e. negative)?
┌───────┴───────┐ │
IF((Data::A1−Data::B1)<0,"Negative","Positive")
└───┬────┘ └───┬────┘
│ └── if false, return "Positive"
│
└── if true, return "Negative"
Result:
However, this will return Positive even if the number is 0, so instead…
┌── from table "Data" do A1-B1
│
│ ┌── is the result less than zero (i.e. negative)?
┌───────┴───────┐ │
IF((Data::A1−Data::B1)<0,"Negative",IF((Data::A1−Data::B1)=0,"Zero","Positive"))
└───┬────┘ │ └─┬──┘ └────┬───┘
if true, return "Negative" ──┘ is equal to 0? ───┘ │ return "Positive"
└── return "Zero"
Best Answer
To be honest, your question is a little unclear, so If I've misunderstood it please clarify.
What you need is a formula in A2 that is something like the following:
The above formula should display in A2 the text you have in cells C1:C5 depending on what's in A1. For example, if A1 = "40-59" then A2 will display whatever text is in cell C3.
However, if you meant that the formula should work so that the text in cell C3 displays in cell A2 if A1 is any value from 40 up to 59, then the formula would be along these lines:
Once again, if I've misunderstood your question, please clarify.