calculate which cells in column A will give me the total of column
 

i have the following spread sheet that reeds like this

Column A B C D

1
2
3
4
5
6
8 6 7
what would the formula be that would indicate which cells (in column A)to
use to achive the total at the bottom of each column, provided that each
number is only used once. this is a basic version of the spreadsheet that i
am trying to calculate.
Regards
Ken

There is no such formula, however if you have solver installed (it comes with
excel/office) you can use it and the technique here

http://tinyurl.com/4doog


Regards,

Peo Sjoblom

"Ken" wrote:

i have the following spread sheet that reeds like this

Column A B C D

1
2
3
4
5
6
8 6 7
what would the formula be that would indicate which cells (in column A)to
use to achive the total at the bottom of each column, provided that each
number is only used once. this is a basic version of the spreadsheet that i
am trying to calculate.
Regards
Ken

Not the format you're looking for, but you could do this.

1. List your numbers in A1:A6.
2. Put one total number (such as 8) in G1.
3. Place this in B1, press ctrl/shift/enter, and fill
down to B6:

=IF(OR(A1+A1=$G$1,SUM(--(A1+$A$1:$A$6=$G$1))=0),"",MAX(IF
(A1+$A$1:$A$6=$G$1,$A$1:$A$6)))

All pairs (except numbers that equal themselves) that add
up to the total in G1 will appear.

HTH
Jason
Atlanta, GA

-----Original Message-----
i have the following spread sheet that reeds like this

Column A B C D

1
2
3
4
5
6
8 6 7
what would the formula be that would indicate which
cells (in column A)to
use to achive the total at the bottom of each column,
provided that each
number is only used once. this is a basic version of the
spreadsheet that i
am trying to calculate.
Regards
Ken

.

Hi Jason
I recieve a blank result from the formula. could i use this same formula on
a bigger scale with a larger range of numbers

"Jason Morin" wrote:

Not the format you're looking for, but you could do this.

1. List your numbers in A1:A6.
2. Put one total number (such as 8) in G1.
3. Place this in B1, press ctrl/shift/enter, and fill
down to B6:

=IF(OR(A1+A1=$G$1,SUM(--(A1+$A$1:$A$6=$G$1))=0),"",MAX(IF
(A1+$A$1:$A$6=$G$1,$A$1:$A$6)))

All pairs (except numbers that equal themselves) that add
up to the total in G1 will appear.

HTH
Jason
Atlanta, GA

-----Original Message-----
i have the following spread sheet that reeds like this

Column A B C D

1
2
3
4
5
6
8 6 7
what would the formula be that would indicate which
cells (in column A)to
use to achive the total at the bottom of each column,
provided that each
number is only used once. this is a basic version of the
spreadsheet that i
am trying to calculate.
Regards
Ken

.

Wow!
What an impressive program. It was exactly what I was looking for. If I can
just make a suggestion, if you want to use the same set of numbers to achieve
different results without repeating any of the numbers. You must delete the
numbers from the original (A1:A8) list that have already been used.

Thank you for the info


"Peo Sjoblom" wrote:

There is no such formula, however if you have solver installed (it comes with
excel/office) you can use it and the technique here

http://tinyurl.com/4doog


Regards,

Peo Sjoblom

"Ken" wrote:

i have the following spread sheet that reeds like this

Column A B C D

1
2
3
4
5
6
8 6 7
what would the formula be that would indicate which cells (in column A)to
use to achive the total at the bottom of each column, provided that each
number is only used once. this is a basic version of the spreadsheet that i
am trying to calculate.
Regards
Ken