How do I say…

[thomasstyron]

Hello All:

I have this formula:

=IF(C8*C9*C9*C10*C11*C12*C13*C14*C15=0,"",SUM(C8:C15))

How can I condense this group, C8*C9*C9*C10*C11*C12*C13*C14*C15, to the
same type of notation as the grouping of C8, C9, C10, …C15 =(C8:C15)? Is
there a way to specify, “if this cell or this cell or this cell etc.” a
shorter way, or do we just have to type out the whole shebang?

Thanks in advance for any help that can be offered.
Best regards to all,
Thomas

 

[Paul Sheppard]

Try

=IF(PRODUCT(C8:C15)=0,"",SUM(C8:C15)

 

[thomasstyron]

Hi Paul:

Thank you for the response.

The "*" notation in this case means "or". I am saying to the cell, if
C8 or C9 or C10...or C13 are zero, then don't show anything in this
cell (untill all of the values are entered).

When I tried your equation and if I skipped a cell, it still computed
the sum of the remaining entered cells. I want the cell blank until all
of the values are entered. This way the user knows that there is a value
missing. If there is a final sum displayed, then they know that the have
entered values in all of the required cells.

Thank you again for your response.

Best Regards,
Thomas
(e-mail address removed)

 

[BenjieLop]

I have some questions regarding your post:

<< The "*" notation in this case means "or". I am saying to the cell
if C8 or C9 or C10...or C13 are zero, then don't show anything in thi
cell (untill all of the values are entered).

When I tried your equation and if I skipped a cell, it still compute
the sum of the remaining entered cells. I want the cell blank until al
of the values are entered. This way the user knows that there is a valu
missing. If there is a final sum displayed, then they know that the hav
entered values in all of the required cells. >>

There are some inconsistent instructions in here. In the firs
paragraph, you mentioned that *"if C8 or C9 or C10...or C13 are zero
then don't show anything in this cell (untill all of the values ar
entered)." * Obviously, this means that if any of the cells in th
mentioned range in Column C contains the number zero (0), then the su
will not be shown until all of the values are entered.

However, In the second paragraph, you mentioned that when you *"skippe
a cell, it still computed the sum of the remaining entered cells ... "*
The way I understand this is -- if you skipped a cell, then tha
particular cell is blank and does not contain the number zero (0).

If I understand your post correctly then, you do not want the sum t
appear if any of the cells (C18:C15) either will contain the number
or will be blank. Am I correct here?

Also, you mentioned *that the sum will not be shown until all of th
values are entered.*. My question here is, how is it determined if al
the values have already been entered? Does that mean that Cell 15 wil
always have a number in it?

Regards

 

[swatsp0p]

This formula will test for both 'Blank' cells and zero values and return
"Missing Data" if either (or both) are true:

=IF(COUNT(C8:C15)=8,IF(PRODUCT(C8:C15)=0,"Missing
Data",SUM(C8:C15)),"Missing Data")

note that a non-numeric entry (e.g. "a") will also return the "Missing
Data" response.

Is this what you are looking for?

Bruce

 

[Dave Peterson]

Maybe just:

=IF(count(C8:c15)<>8,"",SUM(C8:C15))

=count() counts numbers. If your users can type text in those cells:

=IF(counta(C8:c15)<>8,"",SUM(C8:C15))

=counta() counts numbers and text.

 

[Sandy Mann]

There may be better ways but try:

=IF(COUNT(IF(C8:C15>0,C8:C15))<8,"",SUM(C8:C15))

array entered with Ctrl + Shift + Enter

If by

C8 or C9 or C10...or C13 are zero, then don't show anything in this
cell (untill all of the values are entered).

you mean if a cell is empty, ie allow a figure zero entry to be accepted
then simply:

=IF(COUNT(C8:C15)<8,"",SUM(C8:C15))

normally entered should do what you want.



Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk


"thomasstyron" <[email protected]>
wrote in message

 

[swatsp0p]

Dave: note that your formula will return the SUM if one or more cell
contain a zero. Per the OP ("...I am saying to the cell, if C8 or C
or C10...or C13 are zero, then don't show anything in this cell)
returning a sum when there is any zero (or blanks) in the range i
unacceptable, as a non-zero value is expected in every cell.

The OP does not address the instance where the correct entry is a zer
(I guess it will never happen?)

Bruc

 

[Colin2u]

Is there a way to have any given $ amount each month and have only
$100.00 be deducted reguardless the per month total. Meaning if last month
the total is 4500 then $100 is takn out and if this month the total is $30500
then still only $100 is taken out and be placed into a spicific location. If
there is formula to do this type of activity, then I'll like to have it
please.
thankyou.

 

[thomasstyron]

Hello All:

Let me try to address these posts the best I can.

First, BenjieLop: I think you got my initial post and my follow-up wit
Paul mixed up. Paste this into cell A10
=IF(A1*A2*A3*A4*A5*A6*A7*A8*A9=0,"",SUM(A1:A9))
This is the same equation that I am using, but easier to follow as i
is in the first column. Now, just start typing numbers into cells A1 t
A9. This should compute a sum in A10. Now delete, say cell A5. Cell A1
is now blank. When I tried Paul's solution, it computed a final valu
even if a cell was empty. His solution was essentially saying, cell, i
the product of these cells are zero, then the cell that the equation i
in is zero. I was actually explaining on how my equation worked. I
actually does work, I was just trying to condense it up and maybe lear
a shortcut.

I got where my formula is by this response from Gary's Student
http://www.excelforum.com/showthread.php?t=391561
I just added more cells to widen the range of if what equal zero the
show blank.

Bruce: That is pretty neat. I actually may end up incorporating it int
my stuff. But for now, I am still looking for a shortcut for th
underlined:

=IF(_C8*C9*C9*C10*C11*C12*C13*C14*C15_=0,"",SUM(C8:C15))
Again in SUM(C8:C15) we are saying add all of these cells (C8+C9+..C15
up. I want to condense "if C8 or C9 or C10 or..C15 are zero...

Dave: I think you got it! Not exactly how I envisioned it, but it i
essentially doing what I want. Let me try to get this straight though
the equation is saying if any of the cells between C8 and C15 ar
missing (and you specify the number of cells is exactly 8 by <>8), the
show a blank in the equation's cell. If, on the other hand, all value
are present, then gimme' a summation.

And finally Bruce (again): I tried Dave’s solution and when I delete
the value for, say cell C9, C16 actually became blank. Pay dirt! An
you are right; there will never be an instance where the value of an
one of the cells is zero. They are circumferential measurements o
certain body parts (arms, thighs, abdomen, etc.) and so if there is
zero that means there is no body part. Gulp! If the user physicall
insert’s a zero rather than leaving it blank- that is ok I guess.
just didn’t want the user to leave a cell blank.

And again, I may use your solution when I get all of the bugs out of m
current problems.

Whew. You guys are sure smart and helpful. I do appreciate all of th
help.

Best regards,
Thoma

 

[thomasstyron]

Sorry I didn't addresss your post. You guys post faster than I can thin
much less respond to previous posts!

I will try your solution as well.

Thank you.
Thoma

 

[Dave Peterson]

I was confused about what the OP wanted. I thought he wanted to check for empty
cells.

From this portion: I want the cell blank until all of the values are entered.

And I figured that 0's are an ok entry.

If they're not ok, then your warning applies.