M$ Excel
 

I am trying to make a spreadsheet to simplify the calculations for weapon damage values. I have already created a function that will calculate the damage at a specific range, based on size, rate, damage ratio, and damage ratio attenuation. I want to create 1 column that is a spread of 20 numbers, as appears in the components.txt file. I want it to calculate the damage value at each range, and I have it doing that. But, I want it to check each value to see if that range number is greater than the maximum range value that I have set. If it is, then that damage value goes to 0. So I think I would need to use an IF statement. It works for the damage at range values greater than the maximum range, but it does not work for all range values within the maximum range. For those values, it outputs the product function as a text string! I do not know why it would do this, or how to fix it. Here is my IF statement:

=IF(Sheet1!K2<n,0,"=PRODUCT(Sheet1!F2*(Sheet1!G2-(Sheet1!J2*(n-1)))*Sheet1!H2)")

The bold n is variable, and I have 20 different colums for this function. It represents the number of the term for the damage at range value. ie: if the damage spread is:

30 25 20 15 10 0 0 0 0 0 0 0 0

Then 20 is the third term, so the n is replaced by 3 in the C column on sheet 2.

I hope that I have explained this sufficiently for those that could help me to be able to understand it. If not, I can post the spreadsheet file.

Re: M$ Excel
 

I'm no whizz with excel, but have you tried it without the =PRODUCT and the quotes? When I've used the IF statement, I just put in the formula as the conditional result.

Re: M$ Excel
 

The quotes are the problem...its forces what's between them to be treated as a string.

ie.
=4*3 gives you 12
="4*3" gives you 4*3

Askan

Re: M$ Excel
 

If I remove the quotes and/or the PRODUCT, it tells me that the formula has errors. The formula works fine when not in an IF statement.

[ October 24, 2002, 09:41: Message edited by: Imperator Fyron ]

Re: M$ Excel
 

<font size="2" face="Verdana, Helvetica, sans-serif">Fyron,

The problem is the equal sign before the PRODUCT. Take out the equals sign there and the quote marks, leave the equals sign before the IF, and your single formula should work fine.

</font><blockquote><font size="1" face="Verdana, Helvetica, sans-serif">code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">=IF(Sheet1!K2&lt;n,0,PRODUCT(Sheet1!F2 *(Sheet1!G2-(Sheet1!J2*(n-1)))*Sheet1!H2))</pre><hr /></blockquote><font size="2" face="Verdana, Helvetica, sans-serif">Geoschmo

[ October 24, 2002, 13:23: Message edited by: geoschmo ]

Re: M$ Excel
 

Thank you Geo! Is there anything you don't know? (hehe, that rhymes! http://forum.shrapnelgames.com/images/icons/icon10.gif )

Thanks to everyone else that offered solutions too.

Re: M$ Excel
 

New question:

I want to have 1 cell that has the damage at range line from components.txt in it. eg:

20 18 16 14 0 0 0 0 0 0 0 0 0 0 0 0 0

I have the damage at each range calculated in a separate cell. How do I get 1 cell to make a list of numbers based off of those cells?

Re: M$ Excel
 

=CONCATENATE(A1," ",B1," ",C1," ",D1)

Re: M$ Excel
 

Ok, thanks!

Re: M$ Excel
 

I've had the same problem several times, and never did get it to work. I had to go around it by using two steps.

Place =PRODUCT(Sheet1!F2*(Sheet1!G2-(Sheet1!J2*(n-1)))*Sheet1!H2) far off to the right like in the BA2.

Then use =IF(Sheet1! K 2 < n,0,BA2 )". equation was creating a HTML tag, had to put some extra spaces in there

There should be a way to use the entire equation in one step, but I haven't been able to get it to work. As a precaution, there are several other of the formula's in excel that cause the same difficulties.

[ October 24, 2002, 12:26: Message edited by: Atraikius ]