How to get the final "result" to show in one cell? Excell 2002

[Lisa Ann Kashner]

Hello, I am soooo confused with this scenario....

I have checkboxes set up.
cell H8 has: 1st checkbox formula is: =IF(D8=TRUE,60,"")
cell I8 has: 2nd checkbox formula is: =IF(E8=TRUE,120,"")

I would like the final result to show up in H8. For example:
If D8 was checked off - I would like the result to show up in H8.
If E8 was checked off - I would like the result to show up in H8 too.

How is this accomplished? Right now I have 2 total columns BUT I would only
like one

Please help!
Thanks in advance! Lisa Ann

 

[T. Valko]

Not sure what you're trying to do but this *might* work (I'm just
guessing!):

H8:

=IF(E8,120,IF(D8,60,""))

 

[pinmaster]

Hi Lisa Ann

Maybe something like this:

=IF(D8=TRUE,60,"")&IF(E8=TRUE,IF(D8=TRUE," - 120",120),"")

HTH
Jean-Guy

 

[Lisa Ann Kashner]

MVP...

That is exactly what I wanted....., it worked GREAT!!

Thanks! Lisa Ann

 

[Lisa Ann Kashner]

WONDERFUL! Thank you.... I would just love to have you guys "smarts"!!!!

 

[Lisa Ann Kashner]

Hey guys... one more problem that I came across... are you still there?

I came across a situation that I have 4 different check boxes. Each one with
a different value. This is my formula:

H22 CELL CONTAINS:
=IF(E22,100,IF(D22,50,"")&IF(F22,60,IF(G22,90,"")))

The H22 column is set up to display the numbers as: accounting/2 decimal
places/dollar sign.

2 columns are displaying correctly AND 2 columns are not:
E22 displays as $100.00
D22 displays as $ 50.00
BUT...
F22 displays as 60
G22 displays as 90

What am I doing wrong? F22 and G22 are missing the $ and decimal place?

Thank you! Lisa Ann

 

[Earl Kiosterud]

Lisa Ann,

The dollar signs and decimal places are controlled by the formatting in the F22 and G22
cells themselves, not any other cells. Use Format - Cells - Number with those cells
selected, and set it to the accounting or currency format you want. Or just use the $
formatting button on the Formatting Toolbar.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

 

[T. Valko]

That would happen when the linked cells E22 and D22 are FALSE. When those
are FALSE the first part of the formula returns an empty TEXT ("") string
which is concatenated to the result of the second part of the formula. So,
what you get is a result like this: ""60 or ""90. You can't see the "", all
you see is 60 or 90 but it's a TEXT string so the accounting format doesn't
apply.

There's probably a better way to do this if you'd tell use what it is you're
doing. I can sort of guess: the check boxes are for prices or fees and
whichever check box is checked that's the amount you want in the cell? Or ,
you want the highest amount if more than one check box is checked?

 

[Lisa Ann Kashner]

Hello,

What you mentioned below is correct:
......the check boxes are for prices and whichever check box is checked
that's the amount you want in the cell.....

Two of my cells are displaying correctly when checked off - but two are not
(as I mention below).

Thanks soooo much for you thoughts on this!!
Lisa Ann

 

[Lisa Ann]

Hello,

I just wanted to mention that I was wrong in my other "reply" below....

In looking at the sheet further, E22 is the ONLY cell that is displaying
correctly using this formula in cell H22:

=IF(E22,100,IF(D22,50,"")&IF(F22,60,IF(G22,90,"")))

E22 displays as $100.00
D22 as 50
F22 as 60
G22 as 90

But I would like them all to have the "accounting" displaying features like
the $100.00. I have all the cells set with "accounting".

Thanks..., I just thought I would clarify further.

Any help is VERY much appreciated!
Thanks and have a great day,
Lisa Ann

 

[T. Valko]

E22 displays as $100.00

D22 displays as $ 50.00
F22 displays as 60
G22 displays as 90

With D22:G22 being the linked cells try this array formula**:

=MAX(D22:G22*{50;100;60;90})

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Lisa Ann]

Hello,

I put in the array formula in (H22) as you show below, but it gave me the
#VALUE! error now.

I don't understand what you mention about the "key combinations" either. Can
you please explain?

Thanks, Lisa Ann

 

[pinmaster]

Hi Lisa Ann

I tried T. Valko solution and it works fine, but the formula needs to be
entered using Ctrl+Shift+Enter...meaning you need to hold down the Ctrl key
and the Shift Key and then hit the ENTER key, if done correctly you'll see
curly brackets { } on each end of the formula in the formula bar.

you can also try this non array formula:

=IF(D22,50,IF(E22,100,IF(F22,60,IF(G22,90,""))))

Regards!
Jean-Guy



Regards!
Jean-Guy

 

[Lisa Ann]

THANKS A MILLION! It worked just fine! Lisa Ann