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
[email protected]

[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 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. >>

There are some inconsistent instructions in here. In the first 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 are entered)." Obviously, this means that if any of the cells in the mentioned range in Column C contains the number zero (0), then the sum will not be shown until all of the values are entered.

However, In the second paragraph, you mentioned that when you "skipped a cell, it still computed the sum of the remaining entered cells ... " The way I understand this is -- if you skipped a cell, then that 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 to appear if any of the cells (C18:C15) either will contain the number 0 or will be blank. Am I correct here?

Also, you mentioned that the sum will not be shown until all of the values are entered.. My question here is, how is it determined if all the values have already been entered? Does that mean that Cell 15 will 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.


thomasstyron wrote:
>
> 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
>
> --
> thomasstyron
> ------------------------------------------------------------------------
> thomasstyron's Profile: http://www.excelforum.com/member.php...o&userid=25568
> View this thread: http://www.excelforum.com/showthread...hreadid=393933

--

Dave Peterson

[swatsp0p]

Dave: note that your formula will return the SUM if one or more cells contain a zero. Per the OP ("...I am saying to the cell, if C8 or C9 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 is unacceptable, as a non-zero value is expected in every cell.

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

Bruce

[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
[email protected]
Replace@mailinator with @tiscali.co.uk


"thomasstyron" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
> [email protected]
>
>
> --
> thomasstyron
> ------------------------------------------------------------------------
> thomasstyron's Profile:
> http://www.excelforum.com/member.php...o&userid=25568
> View this thread: http://www.excelforum.com/showthread...hreadid=393933
>

[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 with 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 it is in the first column. Now, just start typing numbers into cells A1 to A9. This should compute a sum in A10. Now delete, say cell A5. Cell A10 is now blank. When I tried Paul's solution, it computed a final value even if a cell was empty. His solution was essentially saying, cell, if the product of these cells are zero, then the cell that the equation is in is zero. I was actually explaining on how my equation worked. It actually does work, I was just trying to condense it up and maybe learn 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 then show blank.

Bruce: That is pretty neat. I actually may end up incorporating it into my stuff. But for now, I am still looking for a shortcut for the 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 is 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 are missing (and you specify the number of cells is exactly 8 by <>8), then show a blank in the equation's cell. If, on the other hand, all values are present, then gimme' a summation.

And finally Bruce (again): I tried Dave’s solution and when I deleted the value for, say cell C9, C16 actually became blank. Pay dirt! And you are right; there will never be an instance where the value of any one of the cells is zero. They are circumferential measurements of certain body parts (arms, thighs, abdomen, etc.) and so if there is a zero that means there is no body part. Gulp! If the user physically insert’s a zero rather than leaving it blank- that is ok I guess. I 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 my current problems.

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

Best regards,
Thomas

[thomasstyron]

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

I will try your solution as well.

Thank you.
Thomas

[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.

swatsp0p wrote:
>
> Dave: note that your formula will return the SUM if one or more cells
> contain a zero. Per the OP ("...I am saying to the cell, if C8 or C9
> 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 is
> unacceptable, as a non-zero value is expected in every cell.
>
> The OP does not address the instance where the correct entry is a zero
> (I guess it will never happen?)
>
> Bruce
>
> --
> swatsp0p
>
> ------------------------------------------------------------------------
> swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
> View this thread: http://www.excelforum.com/showthread...hreadid=393933

--

Dave Peterson