Rocksolid Light

Welcome to novaBBS (click a section below)

mail  files  register  newsreader  groups  login

Message-ID:  

6 May, 2024: The networking issue during the past two days has been identified and appears to be fixed. Will keep monitoring.

devel / comp.lang.xharbour / Re: excel: sum of diff values of same cell in same cell

SubjectAuthor
* excel: sum of diff values of same cell in same celltimepro timesheet
`* excel: sum of diff values of same cell in same cellpoopall
 `* excel: sum of diff values of same cell in same celltimepro timesheet
  `* excel: sum of diff values of same cell in same cellpoopall
   `* excel: sum of diff values of same cell in same celltimepro timesheet
    `* excel: sum of diff values of same cell in same cellpoopall
     +- excel: sum of diff values of same cell in same celltimepro timesheet
     `* excel: sum of diff values of same cell in same celltimepro timesheet
      `- excel: sum of diff values of same cell in same celltimepro timesheet

1
Re: excel: sum of diff values of same cell in same cell

<d46400e6-7f6d-4849-b718-a1869dbedb47n@googlegroups.com>

  copy mid

https://www.novabbs.com/devel/article-flat.php?id=10364&group=comp.lang.xharbour#10364

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:a05:6214:29e1:: with SMTP id jv1mr34621806qvb.114.1638255378957;
Mon, 29 Nov 2021 22:56:18 -0800 (PST)
X-Received: by 2002:a05:622a:248:: with SMTP id c8mr48302747qtx.248.1638255378776;
Mon, 29 Nov 2021 22:56:18 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!proxad.net!feeder1-2.proxad.net!209.85.160.216.MISMATCH!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Mon, 29 Nov 2021 22:56:18 -0800 (PST)
In-Reply-To: <62963fbd-85bd-48f1-9ee8-e3c7249988f5n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=2409:4040:e89:c49:30ab:b579:6238:126c;
posting-account=jZAeNwoAAABYWjMRM-FfqKkHfC3Iph0l
NNTP-Posting-Host: 2409:4040:e89:c49:30ab:b579:6238:126c
References: <9aae8b3a-771a-498b-924f-5931629ebd43n@googlegroups.com> <62963fbd-85bd-48f1-9ee8-e3c7249988f5n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <d46400e6-7f6d-4849-b718-a1869dbedb47n@googlegroups.com>
Subject: Re: excel: sum of diff values of same cell in same cell
From: timecost...@gmail.com (timepro timesheet)
Injection-Date: Tue, 30 Nov 2021 06:56:18 +0000
Content-Type: text/plain; charset="UTF-8"
 by: timepro timesheet - Tue, 30 Nov 2021 06:56 UTC

On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:
> On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:
> > hello all:
> >
> > how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.
> >
> > coding:
> > TRY
> > oexcel=createobject('excel.application')
> > CATCH
> > mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
> > dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x)
> > END
> > oexcel=createobject('excel.application')
> > oexcel:workbooks:add()
> > osheet=oexcel:activesheet
> > ...
> > ...
> > do whil !eof()
> > ...
> > for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
> > if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
> > cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
> > next
> > skip
> > endd
> > ...
> > ...
> > *after processing
> > GTSetClipboard(cMemo)
> > xsum(fromcell, tocell)
> > *in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.
> >
> > *********
> > func xsum(xs1,xs2)
> > *********
> > for xx=xs1 to xs2
> > oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
> > next
> >
> > *thank you
> I don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,
>
> Can you give an example of what your spreadsheet looks like, also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.
>
> oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))

thanks poopall:

let me rephrase my query:

in a 'single' cell, i have separate figures for 'cost & discount & tax'
e.g.: from cell D6 to D134 value=str(cost)+chr(10)+str(disc)+chr(10)+str(tax) [single cell will list 3 separate figures in 3 separate rows]
at end of my loop/process, with what formula/sum, can i get individual totals/sum (D6-D134) of 'cost, disc, tax' in the same cell (3 separate rows, but in same/single cell)

also, if the user edits discount in cell D110 , only the sum of discount should auto change.

thanks

p.s.
'also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.'

sure, will try without address(....

Re: excel: sum of diff values of same cell in same cell

<a4b3db81-55de-4898-9094-7916e66931b0n@googlegroups.com>

  copy mid

https://www.novabbs.com/devel/article-flat.php?id=10367&group=comp.lang.xharbour#10367

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:a05:620a:1477:: with SMTP id j23mr5228244qkl.152.1638355456760;
Wed, 01 Dec 2021 02:44:16 -0800 (PST)
X-Received: by 2002:ac8:5a07:: with SMTP id n7mr5568915qta.197.1638355456429;
Wed, 01 Dec 2021 02:44:16 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!proxad.net!feeder1-2.proxad.net!209.85.160.216.MISMATCH!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Wed, 1 Dec 2021 02:44:16 -0800 (PST)
In-Reply-To: <d46400e6-7f6d-4849-b718-a1869dbedb47n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=203.220.120.207; posting-account=zx0oKAkAAABMkjQR74hmeFvHN7cev7io
NNTP-Posting-Host: 203.220.120.207
References: <9aae8b3a-771a-498b-924f-5931629ebd43n@googlegroups.com>
<62963fbd-85bd-48f1-9ee8-e3c7249988f5n@googlegroups.com> <d46400e6-7f6d-4849-b718-a1869dbedb47n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <a4b3db81-55de-4898-9094-7916e66931b0n@googlegroups.com>
Subject: Re: excel: sum of diff values of same cell in same cell
From: lawrence...@gmail.com (poopall)
Injection-Date: Wed, 01 Dec 2021 10:44:16 +0000
Content-Type: text/plain; charset="UTF-8"
 by: poopall - Wed, 1 Dec 2021 10:44 UTC

On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail.com wrote:
> On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:
> > On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:
> > > hello all:
> > >
> > > how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.
> > >
> > > coding:
> > > TRY
> > > oexcel=createobject('excel.application')
> > > CATCH
> > > mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
> > > dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x)
> > > END
> > > oexcel=createobject('excel.application')
> > > oexcel:workbooks:add()
> > > osheet=oexcel:activesheet
> > > ...
> > > ...
> > > do whil !eof()
> > > ...
> > > for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
> > > if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
> > > cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
> > > next
> > > skip
> > > endd
> > > ...
> > > ...
> > > *after processing
> > > GTSetClipboard(cMemo)
> > > xsum(fromcell, tocell)
> > > *in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.
> > >
> > > *********
> > > func xsum(xs1,xs2)
> > > *********
> > > for xx=xs1 to xs2
> > > oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
> > > next
> > >
> > > *thank you
> > I don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,
> >
> > Can you give an example of what your spreadsheet looks like, also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.
> >
> > oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))
> thanks poopall:
>
> let me rephrase my query:
>
> in a 'single' cell, i have separate figures for 'cost & discount & tax'
> e.g.: from cell D6 to D134 value=str(cost)+chr(10)+str(disc)+chr(10)+str(tax) [single cell will list 3 separate figures in 3 separate rows]
> at end of my loop/process, with what formula/sum, can i get individual totals/sum (D6-D134) of 'cost, disc, tax' in the same cell (3 separate rows, but in same/single cell)
>
> also, if the user edits discount in cell D110 , only the sum of discount should auto change.
>
> thanks
>
> p.s.
> 'also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.'
>
> sure, will try without address(....

Can you send an example of what you want the spreadsheet to look like

Re: excel: sum of diff values of same cell in same cell

<e0f28baa-bc44-4696-a2fc-f01a3f91470cn@googlegroups.com>

  copy mid

https://www.novabbs.com/devel/article-flat.php?id=10368&group=comp.lang.xharbour#10368

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:a05:622a:13cf:: with SMTP id p15mr12273078qtk.9.1638420345260;
Wed, 01 Dec 2021 20:45:45 -0800 (PST)
X-Received: by 2002:ac8:7f89:: with SMTP id z9mr12184438qtj.15.1638420345139;
Wed, 01 Dec 2021 20:45:45 -0800 (PST)
Path: i2pn2.org!rocksolid2!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Wed, 1 Dec 2021 20:45:44 -0800 (PST)
In-Reply-To: <a4b3db81-55de-4898-9094-7916e66931b0n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=2409:4040:e89:c49:a5f0:3bbd:6629:3930;
posting-account=jZAeNwoAAABYWjMRM-FfqKkHfC3Iph0l
NNTP-Posting-Host: 2409:4040:e89:c49:a5f0:3bbd:6629:3930
References: <9aae8b3a-771a-498b-924f-5931629ebd43n@googlegroups.com>
<62963fbd-85bd-48f1-9ee8-e3c7249988f5n@googlegroups.com> <d46400e6-7f6d-4849-b718-a1869dbedb47n@googlegroups.com>
<a4b3db81-55de-4898-9094-7916e66931b0n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <e0f28baa-bc44-4696-a2fc-f01a3f91470cn@googlegroups.com>
Subject: Re: excel: sum of diff values of same cell in same cell
From: timecost...@gmail.com (timepro timesheet)
Injection-Date: Thu, 02 Dec 2021 04:45:45 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 79
 by: timepro timesheet - Thu, 2 Dec 2021 04:45 UTC

On Wednesday, December 1, 2021 at 4:14:17 PM UTC+5:30, poopall wrote:
> On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail.com wrote:
> > On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:
> > > On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:
> > > > hello all:
> > > >
> > > > how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.
> > > >
> > > > coding:
> > > > TRY
> > > > oexcel=createobject('excel.application')
> > > > CATCH
> > > > mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
> > > > dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x)
> > > > END
> > > > oexcel=createobject('excel.application')
> > > > oexcel:workbooks:add()
> > > > osheet=oexcel:activesheet
> > > > ...
> > > > ...
> > > > do whil !eof()
> > > > ...
> > > > for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
> > > > if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
> > > > cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
> > > > next
> > > > skip
> > > > endd
> > > > ...
> > > > ...
> > > > *after processing
> > > > GTSetClipboard(cMemo)
> > > > xsum(fromcell, tocell)
> > > > *in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.
> > > >
> > > > *********
> > > > func xsum(xs1,xs2)
> > > > *********
> > > > for xx=xs1 to xs2
> > > > oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
> > > > next
> > > >
> > > > *thank you
> > > I don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,
> > >
> > > Can you give an example of what your spreadsheet looks like, also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.
> > >
> > > oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))
> > thanks poopall:
> >
> > let me rephrase my query:
> >
> > in a 'single' cell, i have separate figures for 'cost & discount & tax'
> > e.g.: from cell D6 to D134 value=str(cost)+chr(10)+str(disc)+chr(10)+str(tax) [single cell will list 3 separate figures in 3 separate rows]
> > at end of my loop/process, with what formula/sum, can i get individual totals/sum (D6-D134) of 'cost, disc, tax' in the same cell (3 separate rows, but in same/single cell)
> >
> > also, if the user edits discount in cell D110 , only the sum of discount should auto change.
> >
> > thanks
> >
> > p.s.
> > 'also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.'
> >
> > sure, will try without address(....
> Can you send an example of what you want the spreadsheet to look like
hi

totals of 'cost, disc, tax' in the same/single cell

i.e. cell D135 should show *separate/individual* sums of cost,disc,tax (D6-D134) as 3 separate rows. (cost,disc,tax are 3 diff. variables)

*e.g.: in D135
1334345 (1334345 being sum of cost D6-D134)
554567 (554... being sum of disc D6-D134)
67418884 (6741... being disc of tax D6-D134)
* thanks

p.s. oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx))+")"
sums only the 'cost'.

Re: excel: sum of diff values of same cell in same cell

<9ac79403-b9af-4d26-bf98-48ab13c10403n@googlegroups.com>

  copy mid

https://www.novabbs.com/devel/article-flat.php?id=10369&group=comp.lang.xharbour#10369

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:a37:d4f:: with SMTP id 76mr15842709qkn.525.1638506073948;
Thu, 02 Dec 2021 20:34:33 -0800 (PST)
X-Received: by 2002:a05:622a:1901:: with SMTP id w1mr18527201qtc.632.1638506073630;
Thu, 02 Dec 2021 20:34:33 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!proxad.net!feeder1-2.proxad.net!209.85.160.216.MISMATCH!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Thu, 2 Dec 2021 20:34:33 -0800 (PST)
In-Reply-To: <e0f28baa-bc44-4696-a2fc-f01a3f91470cn@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=119.18.31.162; posting-account=zx0oKAkAAABMkjQR74hmeFvHN7cev7io
NNTP-Posting-Host: 119.18.31.162
References: <9aae8b3a-771a-498b-924f-5931629ebd43n@googlegroups.com>
<62963fbd-85bd-48f1-9ee8-e3c7249988f5n@googlegroups.com> <d46400e6-7f6d-4849-b718-a1869dbedb47n@googlegroups.com>
<a4b3db81-55de-4898-9094-7916e66931b0n@googlegroups.com> <e0f28baa-bc44-4696-a2fc-f01a3f91470cn@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <9ac79403-b9af-4d26-bf98-48ab13c10403n@googlegroups.com>
Subject: Re: excel: sum of diff values of same cell in same cell
From: lawrence...@gmail.com (poopall)
Injection-Date: Fri, 03 Dec 2021 04:34:33 +0000
Content-Type: text/plain; charset="UTF-8"
 by: poopall - Fri, 3 Dec 2021 04:34 UTC

On Thursday, 2 December 2021 at 3:45:46 pm UTC+11, timec...@gmail.com wrote:
> On Wednesday, December 1, 2021 at 4:14:17 PM UTC+5:30, poopall wrote:
> > On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail.com wrote:
> > > On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:
> > > > On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:
> > > > > hello all:
> > > > >
> > > > > how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.
> > > > >
> > > > > coding:
> > > > > TRY
> > > > > oexcel=createobject('excel.application')
> > > > > CATCH
> > > > > mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
> > > > > dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x)
> > > > > END
> > > > > oexcel=createobject('excel.application')
> > > > > oexcel:workbooks:add()
> > > > > osheet=oexcel:activesheet
> > > > > ...
> > > > > ...
> > > > > do whil !eof()
> > > > > ...
> > > > > for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
> > > > > if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
> > > > > cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
> > > > > next
> > > > > skip
> > > > > endd
> > > > > ...
> > > > > ...
> > > > > *after processing
> > > > > GTSetClipboard(cMemo)
> > > > > xsum(fromcell, tocell)
> > > > > *in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.
> > > > >
> > > > > *********
> > > > > func xsum(xs1,xs2)
> > > > > *********
> > > > > for xx=xs1 to xs2
> > > > > oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
> > > > > next
> > > > >
> > > > > *thank you
> > > > I don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,
> > > >
> > > > Can you give an example of what your spreadsheet looks like, also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.
> > > >
> > > > oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))
> > > thanks poopall:
> > >
> > > let me rephrase my query:
> > >
> > > in a 'single' cell, i have separate figures for 'cost & discount & tax'
> > > e.g.: from cell D6 to D134 value=str(cost)+chr(10)+str(disc)+chr(10)+str(tax) [single cell will list 3 separate figures in 3 separate rows]
> > > at end of my loop/process, with what formula/sum, can i get individual totals/sum (D6-D134) of 'cost, disc, tax' in the same cell (3 separate rows, but in same/single cell)
> > >
> > > also, if the user edits discount in cell D110 , only the sum of discount should auto change.
> > >
> > > thanks
> > >
> > > p.s.
> > > 'also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.'
> > >
> > > sure, will try without address(....
> > Can you send an example of what you want the spreadsheet to look like
> hi
>
> totals of 'cost, disc, tax' in the same/single cell
>
> i.e. cell D135 should show *separate/individual* sums of cost,disc,tax (D6-D134) as 3 separate rows. (cost,disc,tax are 3 diff. variables)
>
> *e.g.: in D135
> 1334345 (1334345 being sum of cost D6-D134)
> 554567 (554... being sum of disc D6-D134)
> 67418884 (6741... being disc of tax D6-D134)
> *
> thanks
>
> p.s. oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx))+")"
> sums only the 'cost'.
Whats the delimiter of the individual cells have you used a comma, as you state you have 3 seperate amounts in there I assume stored in the cell as a string ?

Re: excel: sum of diff values of same cell in same cell

<5be8444c-a9c3-40b1-99d0-94f5dcf3f66cn@googlegroups.com>

  copy mid

https://www.novabbs.com/devel/article-flat.php?id=10370&group=comp.lang.xharbour#10370

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:a05:620a:462b:: with SMTP id br43mr16114428qkb.465.1638517939225;
Thu, 02 Dec 2021 23:52:19 -0800 (PST)
X-Received: by 2002:a05:622a:4c:: with SMTP id y12mr18872128qtw.21.1638517939086;
Thu, 02 Dec 2021 23:52:19 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!border1.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Thu, 2 Dec 2021 23:52:18 -0800 (PST)
In-Reply-To: <9ac79403-b9af-4d26-bf98-48ab13c10403n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=2409:4040:e89:c49:a4a6:d698:6287:1eaf;
posting-account=jZAeNwoAAABYWjMRM-FfqKkHfC3Iph0l
NNTP-Posting-Host: 2409:4040:e89:c49:a4a6:d698:6287:1eaf
References: <9aae8b3a-771a-498b-924f-5931629ebd43n@googlegroups.com>
<62963fbd-85bd-48f1-9ee8-e3c7249988f5n@googlegroups.com> <d46400e6-7f6d-4849-b718-a1869dbedb47n@googlegroups.com>
<a4b3db81-55de-4898-9094-7916e66931b0n@googlegroups.com> <e0f28baa-bc44-4696-a2fc-f01a3f91470cn@googlegroups.com>
<9ac79403-b9af-4d26-bf98-48ab13c10403n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <5be8444c-a9c3-40b1-99d0-94f5dcf3f66cn@googlegroups.com>
Subject: Re: excel: sum of diff values of same cell in same cell
From: timecost...@gmail.com (timepro timesheet)
Injection-Date: Fri, 03 Dec 2021 07:52:19 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 90
 by: timepro timesheet - Fri, 3 Dec 2021 07:52 UTC

On Friday, December 3, 2021 at 10:04:34 AM UTC+5:30, poopall wrote:
> On Thursday, 2 December 2021 at 3:45:46 pm UTC+11, timec...@gmail.com wrote:
> > On Wednesday, December 1, 2021 at 4:14:17 PM UTC+5:30, poopall wrote:
> > > On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail.com wrote:
> > > > On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:
> > > > > On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:
> > > > > > hello all:
> > > > > >
> > > > > > how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.
> > > > > >
> > > > > > coding:
> > > > > > TRY
> > > > > > oexcel=createobject('excel.application')
> > > > > > CATCH
> > > > > > mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
> > > > > > dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x)
> > > > > > END
> > > > > > oexcel=createobject('excel.application')
> > > > > > oexcel:workbooks:add()
> > > > > > osheet=oexcel:activesheet
> > > > > > ...
> > > > > > ...
> > > > > > do whil !eof()
> > > > > > ...
> > > > > > for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
> > > > > > if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
> > > > > > cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
> > > > > > next
> > > > > > skip
> > > > > > endd
> > > > > > ...
> > > > > > ...
> > > > > > *after processing
> > > > > > GTSetClipboard(cMemo)
> > > > > > xsum(fromcell, tocell)
> > > > > > *in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.
> > > > > >
> > > > > > *********
> > > > > > func xsum(xs1,xs2)
> > > > > > *********
> > > > > > for xx=xs1 to xs2
> > > > > > oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
> > > > > > next
> > > > > >
> > > > > > *thank you
> > > > > I don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,
> > > > >
> > > > > Can you give an example of what your spreadsheet looks like, also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.
> > > > >
> > > > > oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))
> > > > thanks poopall:
> > > >
> > > > let me rephrase my query:
> > > >
> > > > in a 'single' cell, i have separate figures for 'cost & discount & tax'
> > > > e.g.: from cell D6 to D134 value=str(cost)+chr(10)+str(disc)+chr(10)+str(tax) [single cell will list 3 separate figures in 3 separate rows]
> > > > at end of my loop/process, with what formula/sum, can i get individual totals/sum (D6-D134) of 'cost, disc, tax' in the same cell (3 separate rows, but in same/single cell)
> > > >
> > > > also, if the user edits discount in cell D110 , only the sum of discount should auto change.
> > > >
> > > > thanks
> > > >
> > > > p.s.
> > > > 'also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.'
> > > >
> > > > sure, will try without address(....
> > > Can you send an example of what you want the spreadsheet to look like
> > hi
> >
> > totals of 'cost, disc, tax' in the same/single cell
> >
> > i.e. cell D135 should show *separate/individual* sums of cost,disc,tax (D6-D134) as 3 separate rows. (cost,disc,tax are 3 diff. variables)
> >
> > *e.g.: in D135
> > 1334345 (1334345 being sum of cost D6-D134)
> > 554567 (554... being sum of disc D6-D134)
> > 67418884 (6741... being disc of tax D6-D134)
> > *
> > thanks
> >
> > p.s. oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx))+")"
> > sums only the 'cost'.
> Whats the delimiter of the individual cells have you used a comma, as you state you have 3 seperate amounts in there I assume stored in the cell as a string ?

str(cost,n)+chr(10)+str(disc,n)+chr(10)+str(tax,n)

so after process (creation of the .xlsx file), each cell shows:
12345
6789
1123666
in a single cell. (12345 being cost, 6789 being disc...)

Re: excel: sum of diff values of same cell in same cell

<267b084c-a104-44df-b056-90757e04de99n@googlegroups.com>

  copy mid

https://www.novabbs.com/devel/article-flat.php?id=10374&group=comp.lang.xharbour#10374

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:ad4:57d2:: with SMTP id y18mr34141426qvx.48.1638769038902;
Sun, 05 Dec 2021 21:37:18 -0800 (PST)
X-Received: by 2002:a05:622a:34c:: with SMTP id r12mr36741575qtw.403.1638769038620;
Sun, 05 Dec 2021 21:37:18 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Sun, 5 Dec 2021 21:37:18 -0800 (PST)
In-Reply-To: <5be8444c-a9c3-40b1-99d0-94f5dcf3f66cn@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=119.18.31.162; posting-account=zx0oKAkAAABMkjQR74hmeFvHN7cev7io
NNTP-Posting-Host: 119.18.31.162
References: <9aae8b3a-771a-498b-924f-5931629ebd43n@googlegroups.com>
<62963fbd-85bd-48f1-9ee8-e3c7249988f5n@googlegroups.com> <d46400e6-7f6d-4849-b718-a1869dbedb47n@googlegroups.com>
<a4b3db81-55de-4898-9094-7916e66931b0n@googlegroups.com> <e0f28baa-bc44-4696-a2fc-f01a3f91470cn@googlegroups.com>
<9ac79403-b9af-4d26-bf98-48ab13c10403n@googlegroups.com> <5be8444c-a9c3-40b1-99d0-94f5dcf3f66cn@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <267b084c-a104-44df-b056-90757e04de99n@googlegroups.com>
Subject: Re: excel: sum of diff values of same cell in same cell
From: lawrence...@gmail.com (poopall)
Injection-Date: Mon, 06 Dec 2021 05:37:18 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Lines: 132
 by: poopall - Mon, 6 Dec 2021 05:37 UTC

On Friday, 3 December 2021 at 6:52:19 pm UTC+11, timec...@gmail.com wrote:
> On Friday, December 3, 2021 at 10:04:34 AM UTC+5:30, poopall wrote:
> > On Thursday, 2 December 2021 at 3:45:46 pm UTC+11, timec...@gmail.com wrote:
> > > On Wednesday, December 1, 2021 at 4:14:17 PM UTC+5:30, poopall wrote:
> > > > On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail.com wrote:
> > > > > On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:
> > > > > > On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:
> > > > > > > hello all:
> > > > > > >
> > > > > > > how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.
> > > > > > >
> > > > > > > coding:
> > > > > > > TRY
> > > > > > > oexcel=createobject('excel.application')
> > > > > > > CATCH
> > > > > > > mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
> > > > > > > dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x)
> > > > > > > END
> > > > > > > oexcel=createobject('excel.application')
> > > > > > > oexcel:workbooks:add()
> > > > > > > osheet=oexcel:activesheet
> > > > > > > ...
> > > > > > > ...
> > > > > > > do whil !eof()
> > > > > > > ...
> > > > > > > for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
> > > > > > > if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
> > > > > > > cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
> > > > > > > next
> > > > > > > skip
> > > > > > > endd
> > > > > > > ...
> > > > > > > ...
> > > > > > > *after processing
> > > > > > > GTSetClipboard(cMemo)
> > > > > > > xsum(fromcell, tocell)
> > > > > > > *in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.
> > > > > > >
> > > > > > > *********
> > > > > > > func xsum(xs1,xs2)
> > > > > > > *********
> > > > > > > for xx=xs1 to xs2
> > > > > > > oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
> > > > > > > next
> > > > > > >
> > > > > > > *thank you
> > > > > > I don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,
> > > > > >
> > > > > > Can you give an example of what your spreadsheet looks like, also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.
> > > > > >
> > > > > > oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))
> > > > > thanks poopall:
> > > > >
> > > > > let me rephrase my query:
> > > > >
> > > > > in a 'single' cell, i have separate figures for 'cost & discount & tax'
> > > > > e.g.: from cell D6 to D134 value=str(cost)+chr(10)+str(disc)+chr(10)+str(tax) [single cell will list 3 separate figures in 3 separate rows]
> > > > > at end of my loop/process, with what formula/sum, can i get individual totals/sum (D6-D134) of 'cost, disc, tax' in the same cell (3 separate rows, but in same/single cell)
> > > > >
> > > > > also, if the user edits discount in cell D110 , only the sum of discount should auto change.
> > > > >
> > > > > thanks
> > > > >
> > > > > p.s.
> > > > > 'also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.'
> > > > >
> > > > > sure, will try without address(....
> > > > Can you send an example of what you want the spreadsheet to look like
> > > hi
> > >
> > > totals of 'cost, disc, tax' in the same/single cell
> > >
> > > i.e. cell D135 should show *separate/individual* sums of cost,disc,tax (D6-D134) as 3 separate rows. (cost,disc,tax are 3 diff. variables)
> > >
> > > *e.g.: in D135
> > > 1334345 (1334345 being sum of cost D6-D134)
> > > 554567 (554... being sum of disc D6-D134)
> > > 67418884 (6741... being disc of tax D6-D134)
> > > *
> > > thanks
> > >
> > > p.s. oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx))+")"
> > > sums only the 'cost'.
> > Whats the delimiter of the individual cells have you used a comma, as you state you have 3 seperate amounts in there I assume stored in the cell as a string ?
> str(cost,n)+chr(10)+str(disc,n)+chr(10)+str(tax,n)
>
> so after process (creation of the .xlsx file), each cell shows:
> 12345
> 6789
> 1123666
> in a single cell. (12345 being cost, 6789 being disc...)

I think the process would be difficult in excel, as you would first have a write a formulae to break up this string into each element, using something like instr() function in excel to locate the delimiter chr(10), then use left , mid and right to break them up, then convert to numeric and then do something else

Is there a reason why each value of cost, disc, tax cannot be written to separate cells, even if these are to another sheet in the same workbook, and then everything else would be relatively easy. The spreadsheet could always be hidden or locked if you did not users to see the content.

Re: excel: sum of diff values of same cell in same cell

<163f7d2d-c156-499d-ad4e-50d628b1425cn@googlegroups.com>

  copy mid

https://www.novabbs.com/devel/article-flat.php?id=10375&group=comp.lang.xharbour#10375

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:ac8:57c2:: with SMTP id w2mr45995399qta.54.1638857899589;
Mon, 06 Dec 2021 22:18:19 -0800 (PST)
X-Received: by 2002:a0c:e98a:: with SMTP id z10mr43284401qvn.43.1638857899468;
Mon, 06 Dec 2021 22:18:19 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Mon, 6 Dec 2021 22:18:19 -0800 (PST)
In-Reply-To: <267b084c-a104-44df-b056-90757e04de99n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=223.229.130.212; posting-account=jZAeNwoAAABYWjMRM-FfqKkHfC3Iph0l
NNTP-Posting-Host: 223.229.130.212
References: <9aae8b3a-771a-498b-924f-5931629ebd43n@googlegroups.com>
<62963fbd-85bd-48f1-9ee8-e3c7249988f5n@googlegroups.com> <d46400e6-7f6d-4849-b718-a1869dbedb47n@googlegroups.com>
<a4b3db81-55de-4898-9094-7916e66931b0n@googlegroups.com> <e0f28baa-bc44-4696-a2fc-f01a3f91470cn@googlegroups.com>
<9ac79403-b9af-4d26-bf98-48ab13c10403n@googlegroups.com> <5be8444c-a9c3-40b1-99d0-94f5dcf3f66cn@googlegroups.com>
<267b084c-a104-44df-b056-90757e04de99n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <163f7d2d-c156-499d-ad4e-50d628b1425cn@googlegroups.com>
Subject: Re: excel: sum of diff values of same cell in same cell
From: timecost...@gmail.com (timepro timesheet)
Injection-Date: Tue, 07 Dec 2021 06:18:19 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Lines: 141
 by: timepro timesheet - Tue, 7 Dec 2021 06:18 UTC

On Monday, December 6, 2021 at 11:07:19 AM UTC+5:30, poopall wrote:
> On Friday, 3 December 2021 at 6:52:19 pm UTC+11, timec...@gmail.com wrote:
> > On Friday, December 3, 2021 at 10:04:34 AM UTC+5:30, poopall wrote:
> > > On Thursday, 2 December 2021 at 3:45:46 pm UTC+11, timec...@gmail.com wrote:
> > > > On Wednesday, December 1, 2021 at 4:14:17 PM UTC+5:30, poopall wrote:
> > > > > On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail..com wrote:
> > > > > > On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:
> > > > > > > On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:
> > > > > > > > hello all:
> > > > > > > >
> > > > > > > > how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.
> > > > > > > >
> > > > > > > > coding:
> > > > > > > > TRY
> > > > > > > > oexcel=createobject('excel.application')
> > > > > > > > CATCH
> > > > > > > > mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
> > > > > > > > dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x)
> > > > > > > > END
> > > > > > > > oexcel=createobject('excel.application')
> > > > > > > > oexcel:workbooks:add()
> > > > > > > > osheet=oexcel:activesheet
> > > > > > > > ...
> > > > > > > > ...
> > > > > > > > do whil !eof()
> > > > > > > > ...
> > > > > > > > for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
> > > > > > > > if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
> > > > > > > > cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
> > > > > > > > next
> > > > > > > > skip
> > > > > > > > endd
> > > > > > > > ...
> > > > > > > > ...
> > > > > > > > *after processing
> > > > > > > > GTSetClipboard(cMemo)
> > > > > > > > xsum(fromcell, tocell)
> > > > > > > > *in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.
> > > > > > > >
> > > > > > > > *********
> > > > > > > > func xsum(xs1,xs2)
> > > > > > > > *********
> > > > > > > > for xx=xs1 to xs2
> > > > > > > > oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
> > > > > > > > next
> > > > > > > >
> > > > > > > > *thank you
> > > > > > > I don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,
> > > > > > >
> > > > > > > Can you give an example of what your spreadsheet looks like, also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.
> > > > > > >
> > > > > > > oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))
> > > > > > thanks poopall:
> > > > > >
> > > > > > let me rephrase my query:
> > > > > >
> > > > > > in a 'single' cell, i have separate figures for 'cost & discount & tax'
> > > > > > e.g.: from cell D6 to D134 value=str(cost)+chr(10)+str(disc)+chr(10)+str(tax) [single cell will list 3 separate figures in 3 separate rows]
> > > > > > at end of my loop/process, with what formula/sum, can i get individual totals/sum (D6-D134) of 'cost, disc, tax' in the same cell (3 separate rows, but in same/single cell)
> > > > > >
> > > > > > also, if the user edits discount in cell D110 , only the sum of discount should auto change.
> > > > > >
> > > > > > thanks
> > > > > >
> > > > > > p.s.
> > > > > > 'also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.'
> > > > > >
> > > > > > sure, will try without address(....
> > > > > Can you send an example of what you want the spreadsheet to look like
> > > > hi
> > > >
> > > > totals of 'cost, disc, tax' in the same/single cell
> > > >
> > > > i.e. cell D135 should show *separate/individual* sums of cost,disc,tax (D6-D134) as 3 separate rows. (cost,disc,tax are 3 diff. variables)
> > > >
> > > > *e.g.: in D135
> > > > 1334345 (1334345 being sum of cost D6-D134)
> > > > 554567 (554... being sum of disc D6-D134)
> > > > 67418884 (6741... being disc of tax D6-D134)
> > > > *
> > > > thanks
> > > >
> > > > p.s. oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx))+")"
> > > > sums only the 'cost'.
> > > Whats the delimiter of the individual cells have you used a comma, as you state you have 3 seperate amounts in there I assume stored in the cell as a string ?
> > str(cost,n)+chr(10)+str(disc,n)+chr(10)+str(tax,n)
> >
> > so after process (creation of the .xlsx file), each cell shows:
> > 12345
> > 6789
> > 1123666
> > in a single cell. (12345 being cost, 6789 being disc...)
> I think the process would be difficult in excel, as you would first have a write a formulae to break up this string into each element, using something like instr() function in excel to locate the delimiter chr(10), then use left , mid and right to break them up, then convert to numeric and then do something else
>
> Is there a reason why each value of cost, disc, tax cannot be written to separate cells, even if these are to another sheet in the same workbook, and then everything else would be relatively easy. The spreadsheet could always be hidden or locked if you did not users to see the content.
* thanks poopall for the efforts:

if each value is saved to an independent cell, the columns (already 42) will increase making it 'not easy' for the user to navigate.
besides seeing all the values (cost, disc, tax, qty...) in the same cell in the same eyeshot, makes it easy for the user to discern.

Re: excel: sum of diff values of same cell in same cell

<7013abc1-b129-480d-b6e1-198bb8f6b2dcn@googlegroups.com>

  copy mid

https://www.novabbs.com/devel/article-flat.php?id=10376&group=comp.lang.xharbour#10376

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:ac8:7d01:: with SMTP id g1mr14132714qtb.175.1639027737858;
Wed, 08 Dec 2021 21:28:57 -0800 (PST)
X-Received: by 2002:a05:622a:609:: with SMTP id z9mr14070890qta.243.1639027737718;
Wed, 08 Dec 2021 21:28:57 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Wed, 8 Dec 2021 21:28:57 -0800 (PST)
In-Reply-To: <267b084c-a104-44df-b056-90757e04de99n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=106.213.60.89; posting-account=jZAeNwoAAABYWjMRM-FfqKkHfC3Iph0l
NNTP-Posting-Host: 106.213.60.89
References: <9aae8b3a-771a-498b-924f-5931629ebd43n@googlegroups.com>
<62963fbd-85bd-48f1-9ee8-e3c7249988f5n@googlegroups.com> <d46400e6-7f6d-4849-b718-a1869dbedb47n@googlegroups.com>
<a4b3db81-55de-4898-9094-7916e66931b0n@googlegroups.com> <e0f28baa-bc44-4696-a2fc-f01a3f91470cn@googlegroups.com>
<9ac79403-b9af-4d26-bf98-48ab13c10403n@googlegroups.com> <5be8444c-a9c3-40b1-99d0-94f5dcf3f66cn@googlegroups.com>
<267b084c-a104-44df-b056-90757e04de99n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <7013abc1-b129-480d-b6e1-198bb8f6b2dcn@googlegroups.com>
Subject: Re: excel: sum of diff values of same cell in same cell
From: timecost...@gmail.com (timepro timesheet)
Injection-Date: Thu, 09 Dec 2021 05:28:57 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 20
 by: timepro timesheet - Thu, 9 Dec 2021 05:28 UTC

[horiz total]
oSheet:Cells(val(left(htot[jc],7)),28):Value="=SUM("+oSheet:Range(oSheet:Cells(val(left(htot[jc],7)),7),oSheet:Cells(val(left(htot[jc],7)),9)):Address( .F.,.F.)+")" && horiz total, cell28=cell7+cell8+cell9
how to code:
cell28=cell7-cell8+cell9
cell28=cell7-cell8-cell9
cell28=cell7-(cell8*cell9)
cell28=(cell7/abc)*((cell8/def)*(cell9)+(cell22))

[column totals (vertical)]
oSheet:Cells(xrow,7):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,14),oSheet:Cells(xrow-1,17)):Address( .F.,.F.)+")"
how to code:
cell7=cell7-cell3+cell22
cell14=cell5-(cell3*cell22)
cellxx=cell19/cell5

regards

p.s.:
i will remove the 'address(.f...'
sometimes back, a guru like you had sent me an example of the 'sum' template with 'address(.f..)' so i simply incorporated it...(albeit, it does not have any adverse effect on the output, so i reckoned, 'if it aint broke...')

Re: excel: sum of diff values of same cell in same cell

<66a29ae5-f64b-46a5-a3ec-0348145d4402n@googlegroups.com>

  copy mid

https://www.novabbs.com/devel/article-flat.php?id=10377&group=comp.lang.xharbour#10377

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:ac8:5fc1:: with SMTP id k1mr15912133qta.303.1639049240651;
Thu, 09 Dec 2021 03:27:20 -0800 (PST)
X-Received: by 2002:ac8:7f89:: with SMTP id z9mr17314461qtj.15.1639049240466;
Thu, 09 Dec 2021 03:27:20 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!border1.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Thu, 9 Dec 2021 03:27:20 -0800 (PST)
In-Reply-To: <7013abc1-b129-480d-b6e1-198bb8f6b2dcn@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=106.213.60.89; posting-account=jZAeNwoAAABYWjMRM-FfqKkHfC3Iph0l
NNTP-Posting-Host: 106.213.60.89
References: <9aae8b3a-771a-498b-924f-5931629ebd43n@googlegroups.com>
<62963fbd-85bd-48f1-9ee8-e3c7249988f5n@googlegroups.com> <d46400e6-7f6d-4849-b718-a1869dbedb47n@googlegroups.com>
<a4b3db81-55de-4898-9094-7916e66931b0n@googlegroups.com> <e0f28baa-bc44-4696-a2fc-f01a3f91470cn@googlegroups.com>
<9ac79403-b9af-4d26-bf98-48ab13c10403n@googlegroups.com> <5be8444c-a9c3-40b1-99d0-94f5dcf3f66cn@googlegroups.com>
<267b084c-a104-44df-b056-90757e04de99n@googlegroups.com> <7013abc1-b129-480d-b6e1-198bb8f6b2dcn@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <66a29ae5-f64b-46a5-a3ec-0348145d4402n@googlegroups.com>
Subject: Re: excel: sum of diff values of same cell in same cell
From: timecost...@gmail.com (timepro timesheet)
Injection-Date: Thu, 09 Dec 2021 11:27:20 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 11
 by: timepro timesheet - Thu, 9 Dec 2021 11:27 UTC

this works, but i need the row,col to be variables:
temp1="E"+alltrim(str(xrow,6)) ; temp2="J"+alltrim(str(xrow,6))

oSheet:cells(xrow,29):formula:="=sum(&temp1:&temp1)-sum(&temp2:&temp2)"

but i need the row,col to be variables (this crashes when writing to xcel)
oSheet:Cells(xrow,xx):formula:="=SUM("+osheet:range(oSheet:cells(xrow,yy),oSheet:cells(xrow,yy))+")"+" - "+"SUM("+oSheet:range(osheet:Cells(xrow,zz),oSheet:Cells(xrow,zz))+")"
* Error BASE/1081 Argument error: + Arguments: ( [ 1] = Type: C Val: =SUM( [ 2] =|
Type: O Val: { TOLEAUTO Object })
*

1
server_pubkey.txt

rocksolid light 0.9.81
clearnet tor