Groups > microsoft.public.excel.programming > #108792

Re: req. for ideas for numeric worksheet

From	GS <gs@v.invalid>
Newsgroups	microsoft.public.excel.programming
Subject	Re: req. for ideas for numeric worksheet
Date	2016-04-23 18:32 -0400
Organization	A noiseless patient Spider
Message-ID	<nfgt05$8us$1@dont-email.me> (permalink)
References	(2 earlier) <nff7dn$cut$1@dont-email.me> <nfg578$h71$1@dont-email.me> <nfgmu7$gqt$1@dont-email.me> <nfgpgk$pjr$1@dont-email.me> <nfgq3u$rhs$1@dont-email.me>

Show all headers | View raw

> On 4/23/2016 2:33 PM, Claus Busch wrote:
>> Hi Mike,
>> Am Sat, 23 Apr 2016 13:49:23 -0700 schrieb Mike S:
>
>>> =(I9-H9)+(L9-K9)+(O9-N9)+(R9-Q9)
>>> If I change L, K, O, N, R, Q to text is there a way to add just 
>>> I-H to
>>> the balance and ignore text entries that I can paste into the 
>>> balance
>>> column formula?
>
>> the function SUM ignores text.
>> Try:
>> =SUM(IF(MOD(COLUMN(H9:R9),3)=0,H9:R9))-SUM(IF(MOD(COLUMN(H9:R9),3)=2,H9:R9))
>> and insert the formula with CTRL+Shift+Enter
>> Regards
>> Claus B.
>
> GS and Claus,
>
> Changing the numeric entry columns to general, entering "EX" where 
> needed, then using SUM worked perfectly. Sum ignores non-numric as 
> advertised and does the math correctly... no more #VALUE cells 
> shouting at me from the page! I had no idea SUM worked that way.
>
> Thank you very much!
> Mike

Glad to be some help...

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

req. for ideas for numeric worksheet Mike S <mscir@yahoo.com> - 2016-04-22 16:43 -0700
  Re: req. for ideas for numeric worksheet GS <gs@v.invalid> - 2016-04-23 00:41 -0400
    Re: req. for ideas for numeric worksheet Mike S <mscir@yahoo.com> - 2016-04-23 00:18 -0700
      Re: req. for ideas for numeric worksheet GS <gs@v.invalid> - 2016-04-23 11:46 -0400
        Re: req. for ideas for numeric worksheet Mike S <mscir@yahoo.com> - 2016-04-23 13:49 -0700
          Re: req. for ideas for numeric worksheet GS <gs@v.invalid> - 2016-04-23 17:04 -0400
            Re: req. for ideas for numeric worksheet Mike S <mscir@yahoo.com> - 2016-04-23 14:15 -0700
          Re: req. for ideas for numeric worksheet Claus Busch <claus_busch@t-online.de> - 2016-04-23 23:33 +0200
            Re: req. for ideas for numeric worksheet Mike S <mscir@yahoo.com> - 2016-04-23 14:43 -0700
              Re: req. for ideas for numeric worksheet GS <gs@v.invalid> - 2016-04-23 18:32 -0400

csiph-web