Total of some cells equal to a specific value

Discuss the spreadsheet application
Post Reply
ssbssb25
Posts: 7
Joined: Wed Feb 21, 2018 11:27 am

Total of some cells equal to a specific value

Post by ssbssb25 »

Hello everyone,
I have a spreadsheet in which there are some random numbers available and I want to find out the specific total.
Let's say I have 4 numbers in colmun 80, 40, 20, 90 in separate rows and I want to find out which rows add to 130.
Any help on this?
Last edited by ssbssb25 on Wed Feb 21, 2018 5:04 pm, edited 1 time in total.
Open office 3.4.1 on windows 7
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Total of some cells

Post by Lupp »

If there are one or more subsets of a set (read multisets instead of sets in your case) of given numbers summing to a specific value (and probably how to find them) is a nontrivial problem in the mathematical theory of computation. I don't know all the details, but theorists list the problem under the term of NP-completeness, meaning there is no possible algorithm for which the amount of time needed expressed depening on the number of given addends could be limited by a polynomial expression[*].
See https://duckduckgo.com/?q=specific+valu ... ffsb&ia=qa
There may be no solution substantially simpler than searching through all the subsets by a kind of backtracking algorithm. Yours may be a case where the search can be stopped having found a first solution - if any.

Anyway the backtracking can be organised by simple code. I will, however, return results within a reasonable time only in case of small numbers of addends. You will find some proposed solutions using the above link.

[*] Not quite precise, simplified.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
ssbssb25
Posts: 7
Joined: Wed Feb 21, 2018 11:27 am

Re: Total of some cells

Post by ssbssb25 »

Lupp wrote:If there are one or more subsets of a set (read multisets instead of sets in your case) of given numbers summing to a specific value (and probably how to find them) is a nontrivial problem in the mathematical theory of computation. I don't know all the details, but theorists list the problem under the term of NP-completeness, meaning there is no possible algorithm for which the amount of time needed expressed depening on the number of given addends could be limited by a polynomial expression[*].
See https://duckduckgo.com/?q=specific+valu ... ffsb&ia=qa
There may be no solution substantially simpler than searching through all the subsets by a kind of backtracking algorithm. Yours may be a case where the search can be stopped having found a first solution - if any.

Anyway the backtracking can be organised by simple code. I will, however, return results within a reasonable time only in case of small numbers of addends. You will find some proposed solutions using the above link.

[*] Not quite precise, simplified.
Sorry Sir, I couldn't understand backtrack algorithm, how to apply it?

I am a total newbie in openoffice.

Is this possible in excel?
Open office 3.4.1 on windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Total of some cells equal to a specific value

Post by Villeroy »

This is possible in any spreadsheet program. Just create a 5th column with the sums and then filter by 130.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Total of some cells equal to a specific value

Post by Lupp »

My turn to be sorry now.

I don't know how to create the "fifth column with the sums" in a simple step.
Assuming only 10 original "random" numbers we have 2^10 (=1024) sums in the generalised sense, 1 less if we don't accept the empty sum, and again 10 less if we insist that sums need to have at least 2 addends. Remain 1013. How to generate?
ssbssb25 wrote:...there are some random numbers available...
should tell us that the OQ didn't want to specialise the task to just 4 original numbers (N from now on). Even assuming he (f/m) only thought of sums of exactly 2 addends, and just missed to tell us, there are (N*N - N)/2 such sums, which makes 45 in the case of N = 10. How to generate?

A solution simplified to the mentioned assumption of exactly TWO addends is attached (made for 20 original numbers).
What was my misconception?
Attachments
aoo92531SelectAddends_TWO_2.ods
(14.17 KiB) Downloaded 88 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Total of some cells equal to a specific value

Post by Villeroy »

ssbssb25 wrote:Let's say I have 4 numbers in colmun 80, 40, 20, 90 in separate rows and I want to find out which rows add to 130.
Attachments
t92531.ods
(43.68 KiB) Downloaded 135 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ssbssb25
Posts: 7
Joined: Wed Feb 21, 2018 11:27 am

Re: Total of some cells equal to a specific value

Post by ssbssb25 »

Sir I am posting a screenshot of a sample sheet, in the let us say I want to find out which rows have a some equal to about 18500-18600. How will I do so?

Image

Also, the file I have to work with is in "csv" extension and this is just a sample screenshot. The original file I have to work with have one column with about hundred row from which I have to find the sum.
Open office 3.4.1 on windows 7
ssbssb25
Posts: 7
Joined: Wed Feb 21, 2018 11:27 am

Re: Total of some cells equal to a specific value

Post by ssbssb25 »

Ok Sir,

I am posting an actual type of sample with which I have to work in this from "difference" column, I have to find out which rows add up-to provide a sum between 98700-98800.

Thanks
Attachments
test.ods
(12.16 KiB) Downloaded 181 times
Open office 3.4.1 on windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Total of some cells equal to a specific value

Post by Villeroy »

menu:Data>Filter>Standard Filter...

Field Name: Difference
Condition: >=
Value: 98700

next row:

Operator: AND
Field Name: Difference
Condition: <=
Value: 98800

[OK}

This will show all rows where the difference is >= 98700 and <= 98800 while hiding any other rows.

---
What you have in C is a simple difference between 2 values. It is not a sum. The use of SUM is obsolete because =B2-A2 gives one value and =SUM(B2-A2) sums up a single value. You can simply use =B2-A2
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ssbssb25
Posts: 7
Joined: Wed Feb 21, 2018 11:27 am

Re: Total of some cells equal to a specific value

Post by ssbssb25 »

Villeroy wrote:menu:Data>Filter>Standard Filter...

Field Name: Difference
Condition: >=
Value: 98700

next row:

Operator: AND
Field Name: Difference
Condition: <=
Value: 98800

[OK}

This will show all rows where the difference is >= 98700 and <= 98800 while hiding any other rows.

---
What you have in C is a simple difference between 2 values. It is not a sum. The use of SUM is obsolete because =B2-A2 gives one value and =SUM(B2-A2) sums up a single value. You can simply use =B2-A2
Thanks Sir, but I wanted row c2 and c5 to be highlighted as there total comes to 98719 which comes between 98700 to 98800.
Attachments
standard filter applied
standard filter applied
Open office 3.4.1 on windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Total of some cells equal to a specific value

Post by Villeroy »

Finally we are getting closer.

The attached file uses TRANSPOSE as an array function.
https://wiki.openoffice.org/wiki/Docume ... _functions
Attachments
t92531.ods
(13.77 KiB) Downloaded 98 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ssbssb25
Posts: 7
Joined: Wed Feb 21, 2018 11:27 am

Re: Total of some cells equal to a specific value

Post by ssbssb25 »

Villeroy wrote:Finally we are getting closer.

The attached file uses TRANSPOSE as an array function.
https://wiki.openoffice.org/wiki/Docume ... _functions
Fantastic Sir, so close to my requirement, let me test out/learn more about how you have applied the formula, just one doubt Sir as this test file had only about 5 rows, my actual file has about 100 rows will this formula be viable then?
Open office 3.4.1 on windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Total of some cells equal to a specific value

Post by Villeroy »

100 rows will be fine. Since this solution requires a transposed reference of column C and a spreadsheet has only 1024 rows, this approach would be limited to 1021 values (or 1024 values if you put the array on a separate sheet).
The formula in D2:H6 tests if 2 values have the same position by utilizing the fact that A1 becomes A2 when copied down and A1 becomes B1 when copied to the right. If the position relative to A1 is the same, then the IF function returns 0. If the positions differ, it adds the value in row #1 of the same column to the value in column C of the same row, for instance $C2+E$1 with absolute column $C and absolute row $1.
For the conditional formatting, I selected the inner area D2:H6 and entered the absolute addresses of the two criteria cells $K$1 and $K$2 with operator "Between" and your "csFoundPair" style.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Total of some cells equal to a specific value

Post by Villeroy »

If a single value between the two limits should be highlighted as well:
D2: =$C2+IF(COLUMN(A1)=ROW(A1);0;D$1)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply