Lookup value based on entry in another cell

[Joah]

I am building a statline for game characters consisting of appropriate values. It has health, skills, etc. For hit points we use a dice type to determine a value. A 6=0, a 8=1, a 10=2, and a 12=3. How would I best create this behaviour in a cell, where when we enter a 6, 8, 10, or 12 we get 0, 1, 2, or 3 in another cell.

Thanks,

[tigeravatar]

Joah,

Welcome to the forum!
Give this a try:

Please Login or Register  to view this content.

[Paul]

Hi Joah, welcome to the forum.

Please take a moment to read the forum rules (link in my signature below) and then amend your thread title to something descriptive of your problem. In this case, perhaps "Lookup value based on entry in another cell".

Once you have done this, please send me a PM and I will remove this request.

Thank you.

[Joah]

Hi Joah, welcome to the forum.

Please take a moment to read the forum rules (link in my signature below) and then amend your thread title to something descriptive of your problem. In this case, perhaps "Lookup value based on entry in another cell".

Once you have done this, please send me a PM and I will remove this request.

Thank you.

I can't seem to find the [edit] button, is it perhaps behind the advertisement? wait a min, I can see that I can replace the title in this reply

I must say that I do believe I have found my friends. How is it possible that something so kin to me has been hidden from me all of my life.

Thanks for your quick reply, it's a lot to chew on an empty stomach but I'll see if I can make it work.

Cheers,

[Joah]

k got it. Seems the advertisement was blocking out the buttons on the bar. Dunno if it's firefox or what. I want to try to analyze the offered solution before I try it, just for fun.

=IF(OR(A1={6,8,10,12}),LOOKUP(A1,{6,8,10,12},{0,1,2,3}),"")

= means something will be added in the cell.
IF is a conditional statement
() brackets serve to isolate the requirements
OR is a conditional modifier of the extended parameters
A1is the cell location
{ } is the extended parameters
LOOKUP is a command to cross reference the extended parameters limited within the brackets

Howd I do?

[Joah]

Bridgekeeper: Stop. Who would cross the Bridge of Death must answer me these questions three, ere the other side he see.
Sir Lancelot: Ask me the questions, bridgekeeper. I am not afraid.
Bridgekeeper: What... is your name?
Sir Lancelot: My name is Sir Lancelot of Camelot.
Bridgekeeper: What... is your quest?
Sir Lancelot: To seek the Holy Grail.
Bridgekeeper: What... is your favourite colour?
Sir Lancelot: Blue.
Bridgekeeper: Go on. Off you go.
Sir Lancelot: Oh, thank you. Thank you very much.
Sir Robin: That's easy.
Bridgekeeper: Stop. Who would cross the Bridge of Death must answer me these questions three, ere the other side he see.
Sir Robin: Ask me the questions, bridgekeeper. I'm not afraid.
Bridgekeeper: What... is your name?
Sir Robin: Sir Robin of Camelot.
Bridgekeeper: What... is your quest?
Sir Robin: To seek the Holy Grail.
Bridgekeeper: What... is the capital of Assyria?
[pause]
Sir Robin: I don't know that.
[he is thrown over the edge into the volcano]
Sir Robin: Auuuuuuuugh.
Bridgekeeper: Stop. What... is your name?
Galahad: Sir Galahad of Camelot.
Bridgekeeper: What... is your quest?
Galahad: I seek the Grail.
Bridgekeeper: What... is your favourite colour?
Galahad: Blue. No, yel...
[he is also thrown over the edge]
Galahad: auuuuuuuugh.
Bridgekeeper: Hee hee heh. Stop. What... is your name?
King Arthur: It is 'Arthur', King of the Britons.
Bridgekeeper: What... is your quest?
King Arthur: To seek the Holy Grail.
Bridgekeeper: What... is the air-speed velocity of an unladen swallow?
King Arthur: What do you mean? An African or European swallow?
Bridgekeeper: Huh? I... I don't know that.
[he is thrown over]
Bridgekeeper: Auuuuuuuugh.
Sir Bedevere: How do know so much about swallows?
King Arthur: Well, you have to know these things when you're a king, you know.

--MontyPython QftHG

[Cutter]

You could simply use:

=(A1-6)/2 or =IF(A1="","",(A1-6)/2)

If A1 can be other values other than 6, 8, 10, 12 then:

=IF(OR(A1={6,8,10,12}),(A1-6)/2,"")

[Joah]

If I enter this function in cell A1 under the f=, there is a yellow warning that appears which states Circular Reference Warning, One or more formulas contain a circular reference and may not calculate correctly. Circular references are any references within a formula that depend upon the results of that same formula. For example, a cell that referes to its own value or a cell that refers to another cell which depends on the original cell's value both contain circular references.

Why do I get the impression that I'm in for a long ride.

[Cutter]

A1 is where the dice value is - the formula goes in any other cell.

[Joah]

Uh, any other cell ..?

[Joah]

Arg, my choices for the day: place my heater core back into my '79 Camaro, try to beat the final boss in Diablo 3, or learn a new program. (I've already done sleep so I can't do that again.)

[Cutter]

I'd go with the Cammie.

[Joah]

Yay your solution works! Now I want more!