Probably simple to some...

Chilli · May 26th, 2002, 04:22 PM

...but I am confuddled.

Im writing a system in Excel which calculates battles where each opponent has specific stats.

Im trying to set it up so that when one opponent attacks, he can choose who they attack and the relevant stats are compared and calculated to determine if a) the target is hit and b) the target is damaged.
the formula i have come up with doesnt work, but it looks as though it should, based on a similar formula which works which doesnt use the lookup feature.
this is the formula which doesnt work:

=if(((vlookup($E$20,Defending,10,true)*K4)<=$G$4-1,1,0))

where E20 is the chosen opponent, K4 is a random variable, the value in column 10 is the opponents agility and the value in G4 is the attackers accuracy.

this is the formula i am currently using, but it is insufficient:

=IF($J$11*K4<=$G$4-1,1,0)

and J11 is the value in column 10 for the lookup.
Knowing me, its just something about the brackets, but I could do with an extra pair of eyes to take a look at this.
If you need to take a look at the entire spreadsheet, please feel free to contact me on msn: stgnchilli@hotmail.com (or email me)

Aladin Akyurek · May 26th, 2002, 04:41 PM

Quote:

On 2002-05-26 15:22, Chilli wrote:
...but I am confuddled.

Im writing a system in Excel which calculates battles where each opponent has specific stats.

Im trying to set it up so that when one opponent attacks, he can choose who they attack and the relevant stats are compared and calculated to determine if a) the target is hit and b) the target is damaged.
the formula i have come up with doesnt work, but it looks as though it should, based on a similar formula which works which doesnt use the lookup feature.
this is the formula which doesnt work:

=if(((vlookup($E$20,Defending,10,true)*K4)<=$G$4-1,1,0))

where E20 is the chosen opponent, K4 is a random variable, the value in column 10 is the opponents agility and the value in G4 is the attackers accuracy.

this is the formula i am currently using, but it is insufficient:

=IF($J$11*K4<=$G$4-1,1,0)

and J11 is the value in column 10 for the lookup.
Knowing me, its just something about the brackets, but I could do with an extra pair of eyes to take a look at this.
If you need to take a look at the entire spreadsheet, please feel free to contact me on msn: stgnchilli@hotmail.com (or email me)

Is it:

=(vlookup($E$20,Defending,10,0)*K4<=$G$4-1)+0

Aladin

Yogi Anand · May 26th, 2002, 04:42 PM

syntax wise the formula should be

=IF(VLOOKUP($E$20,defending,10,TRUE)*K4<=$G$4-1,1,0)

If this still does not work, post back and let us review the logic.

Chilli · May 26th, 2002, 04:47 PM

Quote:

On 2002-05-26 15:42, Yogi Anand wrote:
syntax wise the formula should be

=IF(VLOOKUP($E$20,defending,10,TRUE)*K4<=$G$4-1,1,0)

If this still does not work, post back and let us review the logic.

You are now my God!!!

Thanks Man!

Chilli · May 26th, 2002, 04:59 PM

heh, trying to work at midnight is never good...
a slight variation of it, which dont quite work and my eyelids are fighting me.
once this next one is done, its just a case of c+p changing the odd cell reference.

=IF($B$5>0,IF(VLOOKUP($E$26,Defending,10,TRUE)*K10>($G$5-1)*AND(VLOOKUP($E$26,Defending,10,TRUE)*K10<($G$4+1),1,0)))

Yogi Anand · May 26th, 2002, 05:21 PM

Quote:

On 2002-05-26 15:59, Chilli wrote:
heh, trying to work at midnight is never good...
a slight variation of it, which dont quite work and my eyelids are fighting me.
once this next one is done, its just a case of c+p changing the odd cell reference.

=IF($B$5>0,IF(VLOOKUP($E$26,Defending,10,TRUE)*K10>($G$5-1)*AND(VLOOKUP($E$26,Defending,10,TRUE)*K10<($G$4+1),1,0)))

Hi Chilli:
I know there is a syntax problem with this new formula -- since I don't know the logic behind what you are trying to acconplish, I am a bit handicapped -- all I can do is guess what you are trying to do ... please try

=IF($B$5>0,IF(VLOOKUP($E$26,defending,10,TRUE)*K10>($G$5-1),IF(VLOOKUP($E$26,defending,10,TRUE)*K10<($G$4+1),1,0)))

and see if this formula accomplishes for you what you are trying to get, otherwise post back and we may want to discuss the logic behind your formula and construct it on that basis.

Regards!

Chilli · May 26th, 2002, 05:25 PM

the AND bit was cus i need it within 1 of the value in G4. if it is, then i want the value to be 1.
too bad this isnt like the message board i normally go to, we can upload files.

basically, i am trying to calculate glancing hits and full blows.

the first one u solved was for the full blows, this one is for the glancing hits

Yogi Anand · May 26th, 2002, 05:30 PM

Quote:

On 2002-05-26 16:25, Chilli wrote:
the AND bit was cus i need it within 1 of the value in G4. if it is, then i want the value to be 1.
too bad this isnt like the message board i normally go to, we can upload files.

basically, i am trying to calculate glancing hits and full blows.

the first one u solved was for the full blows, this one is for the glancing hits

Hi Chilli:
Why don't you narrate it for me in words the conditions on which your formula was built, e.g., if B5 is grater than 1, and if the lookup value of ... , otherwise, ... , and, ...

I know you can not upload a file to the board, but if you want to email the file to me, but again with a narrative description of the logic behind the formula, I will get it instantly, review it, and if I have any questions, I can chat with you on the board, or discuss this via email with you.

Regards!

Chilli · May 26th, 2002, 05:41 PM

Ok, the email with the file has been sent.

Ignore all the falses, ill deal with them later.
The Cell i want the formula in is C19 and any in the C column below that

Basically, if B5 is greater than 0, it looks up which ship is being targetted in E20 and references to the Manuever Column for that ship and multiplies it by the random variable. If that value calculated is within 1 of the Accuracy (G4), it gives that cell a value of 1, if not, 0.

Chilli · May 26th, 2002, 05:57 PM

ok, now this is wierd.

it seems to be working now

edit: well almost, it has a slight bug. if you could still try and solve it, it would be a great help

Chilli

[ This Message was edited by: Chilli on 2002-05-26 17:09 ]

May 26th, 2002, 04:42 PM	#3
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	syntax wise the formula should be =IF(VLOOKUP($E$20,defending,10,TRUE)*K4<=$G$4-1,1,0) If this still does not work, post back and let us review the logic. __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 26th, 2002, 04:22 PM	#1
Chilli New Member Join Date: May 2002 Posts: 14	...but I am confuddled. Im writing a system in Excel which calculates battles where each opponent has specific stats. Im trying to set it up so that when one opponent attacks, he can choose who they attack and the relevant stats are compared and calculated to determine if a) the target is hit and b) the target is damaged. the formula i have come up with doesnt work, but it looks as though it should, based on a similar formula which works which doesnt use the lookup feature. this is the formula which doesnt work: =if(((vlookup($E$20,Defending,10,true)K4)<=$G$4-1,1,0)) where E20 is the chosen opponent, K4 is a random variable, the value in column 10 is the opponents agility and the value in G4 is the attackers accuracy. this is the formula i am currently using, but it is insufficient: =IF($J$11K4<=$G$4-1,1,0) and J11 is the value in column 10 for the lookup. Knowing me, its just something about the brackets, but I could do with an extra pair of eyes to take a look at this. If you need to take a look at the entire spreadsheet, please feel free to contact me on msn: stgnchilli@hotmail.com (or email me)

May 26th, 2002, 04:59 PM	#5
Chilli New Member Join Date: May 2002 Posts: 14	heh, trying to work at midnight is never good... a slight variation of it, which dont quite work and my eyelids are fighting me. once this next one is done, its just a case of c+p changing the odd cell reference. =IF($B$5>0,IF(VLOOKUP($E$26,Defending,10,TRUE)K10>($G$5-1)AND(VLOOKUP($E$26,Defending,10,TRUE)*K10<($G$4+1),1,0)))

May 26th, 2002, 05:25 PM	#7
Chilli New Member Join Date: May 2002 Posts: 14	the AND bit was cus i need it within 1 of the value in G4. if it is, then i want the value to be 1. too bad this isnt like the message board i normally go to, we can upload files. basically, i am trying to calculate glancing hits and full blows. the first one u solved was for the full blows, this one is for the glancing hits

May 26th, 2002, 05:41 PM	#9
Chilli New Member Join Date: May 2002 Posts: 14	Ok, the email with the file has been sent. Ignore all the falses, ill deal with them later. The Cell i want the formula in is C19 and any in the C column below that Basically, if B5 is greater than 0, it looks up which ship is being targetted in E20 and references to the Manuever Column for that ship and multiplies it by the random variable. If that value calculated is within 1 of the Accuracy (G4), it gives that cell a value of 1, if not, 0.

May 26th, 2002, 05:57 PM	#10
Chilli New Member Join Date: May 2002 Posts: 14	ok, now this is wierd. it seems to be working now edit: well almost, it has a slight bug. if you could still try and solve it, it would be a great help Chilli [ This Message was edited by: Chilli on 2002-05-26 17:09 ]