Help with a formula

[Diogie]

I'm hoping somenone can help me. I know I know how to do this, but it's not
clicking for me at the moment. I need a formula to do the following:

I need X21 to calculate the total if B19="x" and B21="x" then W19+P21, but
if B21=" " then W19.

I need X28 to calculate the total if B25="x" and B28="x" then W25+P28, but
if B28=" " then W25.

I need X32 to calculate the total if B32="x" and B35="x" then W32+P35, but
if B35=" " then W32.

I need T38 to equal the sum of X21+X28+X32.

I can get the last part, but when I try the first formula, I keep getting
error messages. Can anyone help? I'd really like to get the whole thing in
one formula for a grand total in T38 only but I'll take whatever works.

Thanks for the help.

 

[joeu2004]

I need a formula to do the following:
I need X21 to calculate the total if B19="x" and B21="x" then W19+P21,
but if B21=" " then W19.

You have a space between "" for that "but" case above. I presume you
actually want the null string (no space). Either way, you should be
able to make the necessary corrections to the following.

=if(B21="", W19, if(and(B19="x",B21="x"), W19+P21, 0)

Note that I added the last result (0) to cover the case when neither
of two specified conditions is true. It is prudent to cover that
case, not merely let it default to FALSE. If you would like W19
whenever the second condition is false, this can be simplified to:

=if(and(B19="x",B21="x"), W19+P21, W19)

Or the more esoteric form:

=W19 + P21*and(B19="x",B21="x")

I need X28 to calculate the total if B25="x" and B28="x" then W25+P28, but
if B28=" " then W25.

I need X32 to calculate the total if B32="x" and B35="x" then W32+P35, but
if B35=" " then W32.

Follow the paradigm above.

I need T38 to equal the sum of X21+X28+X32.

I can get the last part, but when I try the first formula, I keep getting
error messages. Can anyone help? I'd really like to get the whole thing in
one formula for a grand total in T38 only but I'll take whatever works.

You could take each IF() expression and put them directly into the
formula in T38. For example:

=if(and(B19="x",B21="x"), W19+P21, W19) +
if(and(B25="x",B28="x"), W25+P28, W25) + [...etc...]

Or:

=W19 + P21*and(B19="x",B21="x") + W25 + P28*and(B26="x",B28="X)
+ [...etc...]

HTH.

 

[David Biddulph]

In X21, =IF(AND(B19="x",B21="x"),W19+P21,IF(B21=" ",W19,"undefined result"))
and similarly for your other formulae.

If you want help it's always better to tell us *what* error messages you are
getting, and what formula (and what input values) you're using when you get
the error, because if we have to guess at the question, we're much less
likely to guess the right answer.

 

[Diogie]

Thanks for your help. I should have done better in my description, but
hopefully I can clarify it better this time.

B19 = Option 1 and is where I would enter the letter X to choose Option 1.
B21 = Add Ons and is where I would enter the letter X if additional items
are requested.
W19 = $15
P21 = $5
X21 needs to calculate the total if the letter X is entered into either or
both B19 and B21.

If B19 and B21 are left blank, then X21 needs to be blank as well.

For example, if I enter X in B19 but not in B21 then the total in X21 needs
to be $15.
If I enter X in B19 and B21, then the total in X21 needs to be $20.


Since I have 3 options I can repeat that formula two more times then add
X21, X28, and X35.

I hope I've explained it better this time.

 

[Diogie]

Thanks, this is close. If B19 nor B21 ="x" I need X21 be left blank.
--
Diogie

I need a formula to do the following:
I need X21 to calculate the total if B19="x" and B21="x" then W19+P21,
but if B21=" " then W19.

You have a space between "" for that "but" case above. I presume you
actually want the null string (no space). Either way, you should be
able to make the necessary corrections to the following.

=if(B21="", W19, if(and(B19="x",B21="x"), W19+P21, 0)

Note that I added the last result (0) to cover the case when neither
of two specified conditions is true. It is prudent to cover that
case, not merely let it default to FALSE. If you would like W19
whenever the second condition is false, this can be simplified to:

=if(and(B19="x",B21="x"), W19+P21, W19)

Or the more esoteric form:

=W19 + P21*and(B19="x",B21="x")

I need X28 to calculate the total if B25="x" and B28="x" then W25+P28, but
if B28=" " then W25.

I need X32 to calculate the total if B32="x" and B35="x" then W32+P35, but
if B35=" " then W32.

Follow the paradigm above.

I need T38 to equal the sum of X21+X28+X32.

I can get the last part, but when I try the first formula, I keep getting
error messages. Can anyone help? I'd really like to get the whole thing in
one formula for a grand total in T38 only but I'll take whatever works.

You could take each IF() expression and put them directly into the
formula in T38. For example:

=if(and(B19="x",B21="x"), W19+P21, W19) +
if(and(B25="x",B28="x"), W25+P28, W25) + [...etc...]

Or:

=W19 + P21*and(B19="x",B21="x") + W25 + P28*and(B26="x",B28="X)
+ [...etc...]

HTH.

 

[Diogie]

This worked!! Thanks!!
--
Diogie

I need a formula to do the following:
I need X21 to calculate the total if B19="x" and B21="x" then W19+P21,
but if B21=" " then W19.

You have a space between "" for that "but" case above. I presume you
actually want the null string (no space). Either way, you should be
able to make the necessary corrections to the following.

=if(B21="", W19, if(and(B19="x",B21="x"), W19+P21, 0)

Note that I added the last result (0) to cover the case when neither
of two specified conditions is true. It is prudent to cover that
case, not merely let it default to FALSE. If you would like W19
whenever the second condition is false, this can be simplified to:

=if(and(B19="x",B21="x"), W19+P21, W19)

Or the more esoteric form:

=W19 + P21*and(B19="x",B21="x")

I need X28 to calculate the total if B25="x" and B28="x" then W25+P28, but
if B28=" " then W25.

I need X32 to calculate the total if B32="x" and B35="x" then W32+P35, but
if B35=" " then W32.

Follow the paradigm above.

I need T38 to equal the sum of X21+X28+X32.

I can get the last part, but when I try the first formula, I keep getting
error messages. Can anyone help? I'd really like to get the whole thing in
one formula for a grand total in T38 only but I'll take whatever works.

You could take each IF() expression and put them directly into the
formula in T38. For example:

=if(and(B19="x",B21="x"), W19+P21, W19) +
if(and(B25="x",B28="x"), W25+P28, W25) + [...etc...]

Or:

=W19 + P21*and(B19="x",B21="x") + W25 + P28*and(B26="x",B28="X)
+ [...etc...]

HTH.

 

[Diogie]

I was wrong...it almost works. I still need X21 to be blank if neither B19
nor B21 have an X in them (both cells empty).
--
Diogie

I need a formula to do the following:
I need X21 to calculate the total if B19="x" and B21="x" then W19+P21,
but if B21=" " then W19.

You have a space between "" for that "but" case above. I presume you
actually want the null string (no space). Either way, you should be
able to make the necessary corrections to the following.

=if(B21="", W19, if(and(B19="x",B21="x"), W19+P21, 0)

Note that I added the last result (0) to cover the case when neither
of two specified conditions is true. It is prudent to cover that
case, not merely let it default to FALSE. If you would like W19
whenever the second condition is false, this can be simplified to:

=if(and(B19="x",B21="x"), W19+P21, W19)

Or the more esoteric form:

=W19 + P21*and(B19="x",B21="x")

I need X28 to calculate the total if B25="x" and B28="x" then W25+P28, but
if B28=" " then W25.

I need X32 to calculate the total if B32="x" and B35="x" then W32+P35, but
if B35=" " then W32.

Follow the paradigm above.

I need T38 to equal the sum of X21+X28+X32.

I can get the last part, but when I try the first formula, I keep getting
error messages. Can anyone help? I'd really like to get the whole thing in
one formula for a grand total in T38 only but I'll take whatever works.

You could take each IF() expression and put them directly into the
formula in T38. For example:

=if(and(B19="x",B21="x"), W19+P21, W19) +
if(and(B25="x",B28="x"), W25+P28, W25) + [...etc...]

Or:

=W19 + P21*and(B19="x",B21="x") + W25 + P28*and(B26="x",B28="X)
+ [...etc...]

HTH.

 

[joeu2004]

B19 = Option 1 and is where I would enter the letter X to choose Option 1.
B21 = Add Ons and is where I would enter the letter X if additional items
are requested.
W19 = $15
P21 = $5
X21 needs to calculate the total if the letter X is entered into either or
both B19 and B21.
If B19 and B21 are left blank, then X21 needs to be blank as well.

For example, if I enter X in B19 but not in B21 then the total in X21
needs to be $15.
If I enter X in B19 and B21, then the total in X21 needs to be $20.

What if B21 is "x", but not B19: $5?

Probably not. But if that is what you want, try:

=if(or(B19="x",B21="x), W19*(B19="x") + P21*(B21="x"), "")

But if you want "" if B19 is not "x" regardless, try:

=if(B19="x", W19 + P21*(B21="x"), "")

 

[David Biddulph]

You said you keep getting errors.
You haven't told us what formula you were using, what input values, or what
error you were getting, so we *still* can't tell you what was wrong with
your own formula.

But as for suggesting a new formula, it sounds as if for X21 you want
=IF(AND(B19="x",B21="x"),W19+P21,IF(OR(B19="x",B21="x"),W19,""))
You hadn't said what you want if the value in B19 or B21 is anything other
than "x" or blank, but I've assumed that you want any other value treated
the same as blank.

 

[Diogie]

The last formula worked. The Key cell is B19 so if B19 does not contain an X
then I want X21 to be blank. If B19 contains an X then it should be $15, and
if both B19 and B21 contain an X, then the total in X21 should be 20. If B19
is blank and B21 contains an X, then X21 should be blank. B21 is only valid
if X19 is selected (contains an X). I apologize for being so confusing, my
brain was fried from working and fighting with this issue and I know I made
it harder than it really was. =if(B19="x", W19 + P21*(B21="x"), "") seems to
work so I really do appreciate your help. Thanks so much and I hope you have
a great holiday season.

 

[Diogie]

The Key cell is B19 so if B19 does not contain an X then I want X21 to be
blank. If B19 contains an X then it should be $15, and if both B19 and B21
contain an X, then the total in X21 should be 20. If B19 is blank and B21
contains an X, then X21 should be blank. B21 is only valid if X19 is
selected (contains an X). I apologize for being so confusing, my brain was
fried from working and fighting with this issue and I know I made it harder
than it really was. I have Windows Vista and all it would tell me is that I
have an error in my formula did I want to fix it my self or to select help
for more information. When I selected help, it took me to a list of things
to try to find what the issue was and what the fix was. I did this all day
yesterday with zero success. The only clue I would get is that when I
clicked OK to fix myself, logical2 would be bolded (telling me there was an
issue with logical2 I guess). I appreciate your help with this and sorry I
didn't explain it better sooner.

=if(B19="x", W19 + P21*(B21="x"), "") seems to work so far. Thanks so much
and I hope you have a great holiday season.

 

[Diogie]

Hoping I can trouble you one more time. The formula is working and now I'd
like to highlight cells X21, X28, and X35 when their value is equal to or
greater than $15.00. I can get it to hightlight but it highlights when the
cell value is less than $15.00 as well. I don't know if it's because the
cell contains the formula =if(B19="x", W19 + P21*(B21="x"), "") or what.

When B19 contains an X, X21 results in $15.00 and at this point I'd like X21
to automatically highlight. When B19 does not contain an X, then X21 remains
blank. I would like it to remain unhighlighted.

I've tried making the results of X21 show 0 instead of blank, but that
hasn't worked either. Any suggestions? I'm using Windows Vista Office Home
and Student 2007.

Thanks again for all your help.

 

[joeu2004]

The formula is working and now I'd like to highlight cells X21, X28, and
X35 when their value is equal to or greater than $15.00.

First, I suspect that is not exactly the condition that you are
interested. I suspect you are interested highlighting X21, for
example, when its value is greater than or equal to W19, the "base"
price. Since X28 and X35 depend on different "base" prices (W25 and
W32), I think it would be imprudent to assume that they are all the
same, namely $15.

When B19 contains an X, X21 results in $15.00 and at this point I'd like X21
to automatically highlight. When B19 does not contain an X, then X21 remains
blank. I would like it to remain unhighlighted.
[....]
I'm using Windows Vista Office Home and Student 2007.

I use Excel 2003. I do know if Excel 2007 is different in this
respect.

There are a couple ways to accomplish your goal using
Format>Conditional Formatting. I assume that is what you are doing
now. Perhaps all that needs to change is the conditional formula.
For X21, I would choose the following elements: "cell value is",
"greater than or equal to", with the formula "=$W$19". Alternatively,
"=$B$19="x".

Replace $W$19 (or $B$19) with $W$25 and $W$32 (or $B$25 or $B$32) in
the conditional formula for X28 and X35 respectively.

HTH. If not, please post details about the condition format set-up
that you are using.