Multiple functions in a cell help

M

Mikjall777

I am using Excel 2003 and having trouble figuring out the formulas. What I am
trying to do is add a total of 6 cells in a row. Each cell has a different
formula that comes up with a certain value once all cells are added together.

Example:
cell A= inserted value*10
cell B= inserted value*2
cell C= inserted value
cell D= inserted value*5
cell E= inserted value*3
cell F=inserted value/20

In cell G, I want to add cells a through f except that cell f has multiple
"if then" statements.
Cell f: if inserted value/20 <10 then I want to add a
number to the sum of cells a through e
if inserted value/20 is between 11 and 15 I
don't want to add a certain number to the sum of cells a through e
if inserted value/20 is between 16 and 20 I want
to subtract a certain number to the sum of cells a through e

Anyhelp on this is greatly appreciated! Hopefully I explained what I want to
do without confusing everyone as much as it's confusing to me:)
 
M

Mark

I am not certain I am interpreting this correctly, but it sounds like
you want to have a formula in cell F? and be able to allow a numerical
entry into that cell as well. If that is what you are trying to do, it
is not possible as far as I know.
 
M

Mikjall777

Mark,

I actually want the formula in cell g. Cells a through f should actually be
an inserted value and NOT inserted value with the math (that should be part
of the formula for cell g). Cell g should have some type of formula (without
any inserted values) that will function something like the following:

(inserted value of cell a*10) + (inserted value of cell b*2) +
(inserted value of cell c) + (inserted value of cell d*5) + (inserted value
of cell e*3) + (if (inserted value of cell f/20) < =10 then add 5 OR if
(inserted value of cell f/20) => 11 but <=20 then don't add anything OR if
(inserted value of cell f/20 =>21 but <= 30 then subtract 5)

Hopefully this clears it up more. If not, I'll keep trying to explain in a
better way of what I'm looking for.

Thanks for the help!
Mike
 
M

Mark

Mikjall777 said:
Mark,

I actually want the formula in cell g. Cells a through f should actually be
an inserted value and NOT inserted value with the math (that should be part
of the formula for cell g). Cell g should have some type of formula (without
any inserted values) that will function something like the following:

(inserted value of cell a*10) + (inserted value of cell b*2) +
(inserted value of cell c) + (inserted value of cell d*5) + (inserted value
of cell e*3) + (if (inserted value of cell f/20) < =10 then add 5 OR if
(inserted value of cell f/20) => 11 but <=20 then don't add anything OR if
(inserted value of cell f/20 =>21 but <= 30 then subtract 5)

That makes much more sense, here goes...

Assuming that your data resides in Cells A1:F1

Enter the following formula into G1:

=SUM(A1*10,B1*2,C1,D1*5,E1*3)+IF(F1/20<=10,F1+5,IF(AND(F1/20>=11,F1/20<=20),F1,IF(AND(F1/20>=21,F1/20<=30),F1-5,F1)))

I hope that works as I doubt you are going to reply tonight and it will
be some time before I am on the net again.

Best wishes....
 
M

Mark

(inserted value of cell a*10) + (inserted value of cell b*2) +
(inserted value of cell c) + (inserted value of cell d*5) + (inserted value
of cell e*3) + (if (inserted value of cell f/20) < =10 then add 5 OR if
(inserted value of cell f/20) => 11 but <=20 then don't add anything OR if
(inserted value of cell f/20 =>21 but <= 30 then subtract 5)

That makes much more sense, here goes...

Assuming that your data resides in Cells A1:F1

Enter the following formula into G1:

=SUM(A1*10,B1*2,C1,D1*5,E1*3)+IF(F1/20<=10,F1+5,IF(AND(F1/20>=11,F1/20<=20),F1,IF(AND(F1/20>=21,F1/20<=30),F1-5,F1)))

I hope that works as I doubt you are going to reply tonight and it will
be some time before I am on the net again.

Best wishes....
 
M

Mikjall777

Mark,

Thank you for the help! That was almost what I was looking for but with your
help, I was able to fine tune it exactly.

Thanks again!
Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top