Excel Formula help

Dell / 3100
February 8, 2009 at 14:37:30
Specs: Windows XP, 2.4
Hello,

First time posting here so I want to apologize in advance if this is in the wrong location or anything else I might have done incorrectly.

I am trying to assign a numerical value in each column as follows:

Column A (Row 1-4): Any number 1-20=3
Column B (Row 1-4): Any number 1-20=2
Column C (Row 1-4): Any number 1-20=1
Column D (Row 1-4): Any number 1-20=0

Example:

Column A Row 1: 6 (value=3)
Column A Row 2: 3 (value=3)
Column A Row 3: 6 (value=3)
Column A Row 4: 2 (value=3)

Then

Column B Row 1: 5 (value=2)
Column B Row 2: 5 (value=2)
Column B Row 3: 5 (value=2)
Column B Row 4: 6 (value=2)

Then

Column C Row 1: 6 (value=1)
Column C Row 2: 3 (value=1)
Column C Row 3: 6 (value=1)
Column C Row 4: 4 (value=1)

Then

Column D Row 1: 5 (value=0)
Column D Row 2: 5 (value=0)
Column D Row 3: 5 (value=0)
Column D Row 4: 6 (value=0)

Then have the sum of all columns appear as follows:

Column E Row 1: 6,5,3,2

Because 6=10, 5=6, 3=4, 2=3

Does this make sense? I am not sure if this is even in the scope of excel, but it is worth a shot.

Thanks in advance,

Alex



See More: Excel Formula help

Report •


#1
February 8, 2009 at 17:05:16
Let's take in pieces:

re:
I am trying to assign a numerical value in each column as follows:

Column A (Row 1-4): Any number 1-20=3
Column B (Row 1-4): Any number 1-20=2
Column C (Row 1-4): Any number 1-20=1
Column D (Row 1-4): Any number 1-20=0

etc.

Unless you use VBA programming (Macros) you can't enter one value in a cell and have a different value show up. You can however use Excel functions place a specific value in a cell based on what is in another cell.

For example, put this formula in F1 and any number in A1 from 1 - 20 will result in a 3 showing F1:

=IF(AND(A1>=1,A1<=20),3,"")

Note: You didn't say what you wanted to show if the number wasn't 1 - 20, so I assumed the cell should be empty.

re: Then have the sum of all columns appear as follows:

Column E Row 1: 6,5,3,2

Because 6=10, 5=6, 3=4, 2=3

I have no clue what you are saying here. None of the columns of original numbers or results sum to the values given. Please clarify.


Report •

#2
February 8, 2009 at 18:48:24
Unless you use VBA programming (Macros) you can't enter one value in a cell and have a different value show up.

How would I set it up usin Visual Basic? Is it possible?

Note: You didn't say what you wanted to show if the number wasn't 1 - 20, so I assumed the cell should be empty.

There will never be a number greater than 20 inputted.


re: Then have the sum of all columns appear as follows:

Column E Row 1: 6,5,3,2

I have no clue what you are saying here. None of the columns of original numbers or results sum to the values given. Please clarify.


Example:

Column A Row 1: 6 (value=3)
Column A Row 2: 3 (value=3)
Column A Row 3: 6 (value=3)
Column A Row 4: 2 (value=3)

Then

Column B Row 1: 5 (value=2)
Column B Row 2: 5 (value=2)
Column B Row 3: 5 (value=2)
Column B Row 4: 6 (value=2)

Then

Column C Row 1: 6 (value=1)
Column C Row 2: 3 (value=1)
Column C Row 3: 6 (value=1)
Column C Row 4: 4 (value=1)

Then

Column D Row 1: 5 (value=0)
Column D Row 2: 5 (value=0)
Column D Row 3: 5 (value=0)
Column D Row 4: 6 (value=0)

Because 6=10, 5=6, 3=4, 2=3

All of the 6's (using their associated value) add to 10.

Make any more sense?

Thanks for the quick response. Sorry this is so confusing. Hopefully I am getting my point across.

Let me try it this way. I am trying to input a multiple numbers in into a 4 row by 4 column section of Excel and have the spread sheet automatically tell me which number is most common, 2nd most common and 3rd most common to that group appear in another single cell using X,X,X format. Does that make sense?

So it would look something like this:

ColumnA ColumnB ColumnC ColumnD ColumnE
Row1: 4 4 3 5 4,3,7
Row2: 4 3 7 8
Row3: 4 3 7 2
Row4: 4 3 7 8

Again, sorry for any headache that may come of this and thanks for helping.

Regards,

Alex


Report •

#3
February 8, 2009 at 18:50:24
Sorry my example didn't really come out. Hopefully you can see what I am trying to accomplish. The vertical row of 4's should be under Column A and the next vertical row should be under Column B, etc...

Report •

Related Solutions

#4
February 9, 2009 at 18:27:34
re: "I am trying to input a multiple numbers in into a 4 row by 4 column section of Excel and have the spread sheet automatically tell me which number is most common, 2nd most common and 3rd most common to that group appear in another single cell using X,X,X format. Does that make sense?"

That makes sense, but I don't see how that relates to the examples of 1-20 in Column A gives a value of 3, etc. As I said in my earlier response, you can't enter one value and have a different value show up in that same cell without using VBA code.

However, if all you want to do is find the 1st, 2nd and 3rd most common values in your 4 x 4 table, do this:


1 - Select A1:D4 and give it a name of Rng
2 - In F1 enter =MODE(Rng) to get the most common value (4, in your example)
3 - In F2 put the array ** formula:

{=IF(ROWS($1:2)<=COUNT(1/FREQUENCY(Rng,Rng)),MODE(IF(COUNTIF(F$1:F1,Rng)=0,Rng)),"")}

to get the second most common value. (3)

** Do not type the outer-most brackets. Array formulas are entered by using
Ctrl-Shift-Enter to get the outer-most brackets.
4 - Drag F2 down to F3 to get the 3rd most common value. (7)
5 - In E1 enter =F1&","&F2&","&F3 to get 4,3,7

P.S. I can't take full credit for this answer - I had some expert help.


Report •

#5
February 10, 2009 at 05:48:06
Wow that is what I need. I am having an issue though. It is only giving me the most common number (4) in F1, F2 and F3 and E1 is showing 4,4,4. Any idea what this problem is? Again this is exactly what I am looking for and you have be a great help!

Thanks again,

Alex


Report •

#6
February 10, 2009 at 09:57:05
The only way I can get 4,4,4 is to not follow my instructions of entering the formula in F2 as an "array formula".

The formula must be entered using Ctrl-Shift-Enter everytime you enter it or it will not work.


Report •

#7
February 10, 2009 at 10:17:57
DerbyDad -

Thanks for all the help. That was my first time using an "array formula" so the ctrl+shift+enter thing threw me. It is working wonderfully now. Thanks a bunch!!


Report •

#8
February 10, 2009 at 10:37:08
How about another curve ball?

It doesn't seem to work if there are blank spaces within the 16 cells. Is there a work around for this?

A B C D
1: 4
2: 4 3
3: 4 3 7 2
4: 4 3 7 8

Comes out 4,0,3 Instead of 4,3,7

Is there a way to not include blank spaces in the formula when needed?

Thanks again!

Alex


Report •

#9
February 10, 2009 at 11:38:24
Sorry, but I couldn't get it to work very well with blank cells either. I might play (and ask around) some more, but I'm not confident I'll find a solution.

Report •


Ask Question