Excel tips and tricks

Ghoulish Delight · 09-22-2005, 09:22 AM

I'm using Excel a lot at work. All of our test results are being put into some huge, complex matrices. I've got some questions on how to get some things done, plus I just accidentally found a nifty feature.

First, the nifty feature. If you're in a column that's got some data, and some blank spaces in between the data, holding ctrl and pressing the up or down arrow will bring you to the next cell in that column that has data in it. Great for fast scrolling to your data points. I presume it works for left and right in rows as well.

Now, for my quesiton. Let's say you have two columns. Column A will be either blank or say "yes" for each row. Column B has numerical data for each row. Here's what I want to accomplish. For each row, if Column A="yes", add the data in column B to a total. I can write it in code, but I can't wrap my brain around doing it in excel without a long formula like this: IF(A0="yes", B0,0)+IF(A1="yes", B1,0)+IF(A2="yes",B2,0)+...etc on down the rows. There's got to be a better solution.

Kevy Baby · 09-22-2005, 09:32 AM

Damn you GD. Now I am not going to be able to get any work done because I will be trying to think of the solution for this.

Hopefully somebody will save my day and post a solution soon.

scaeagles · 09-22-2005, 09:43 AM

Ditto Kevy.

scaeagles · 09-22-2005, 09:58 AM

My solution, if possible.

I would add another column C. Column C has the following formula in each cell in the column -

=if (a1="yes",b1,"")

When copying the formula, of course the cell references change.

At the bottom of column C, a simple sum can be used for the range of data. This way, whenever the value of column A changes to or from yes, the data in column C is updated appropriately, and thus the total.

Of course, that only works if you can add column C.

Ponine · 09-22-2005, 09:58 AM

could you create a new column, even possbily hidden that would say
(If A=yes, then..)
and have it put the positive value in a new column, then add all the values in that column into a cell for your total?

Ghoulish Delight · 09-22-2005, 09:59 AM

Yeah, I've thought of that solution. That'll be my fall back, but I'm still hoping for a more elegant solution that doesn't require the extra column

scaeagles · 09-22-2005, 10:01 AM

Well, you just want everything, don't you?

Moonliner · 09-22-2005, 05:15 PM

Hummmm....

If we have

A B
1 yes 1
2 null 5
3 yes 2
4 null 7

Then try:

=SUMPRODUCT((B1:B4)*(A1:A4="yes"))

How's that?

Note: I just added "null" to help the spacing.

Ghoulish Delight · 09-22-2005, 11:11 PM

Give the man a see-gar, it works!

scaeagles · 09-23-2005, 06:52 AM

I bow to your Excel-ence, Moonliner.

09-22-2005, 09:22 AM	#1
Ghoulish Delight I Floop the Pig Join Date: Jan 2005 Location: Alternative Swankstyle Posts: 19,348	Excel tips and tricks I'm using Excel a lot at work. All of our test results are being put into some huge, complex matrices. I've got some questions on how to get some things done, plus I just accidentally found a nifty feature. First, the nifty feature. If you're in a column that's got some data, and some blank spaces in between the data, holding ctrl and pressing the up or down arrow will bring you to the next cell in that column that has data in it. Great for fast scrolling to your data points. I presume it works for left and right in rows as well. Now, for my quesiton. Let's say you have two columns. Column A will be either blank or say "yes" for each row. Column B has numerical data for each row. Here's what I want to accomplish. For each row, if Column A="yes", add the data in column B to a total. I can write it in code, but I can't wrap my brain around doing it in excel without a long formula like this: IF(A0="yes", B0,0)+IF(A1="yes", B1,0)+IF(A2="yes",B2,0)+...etc on down the rows. There's got to be a better solution. __________________ 'He who receives an idea from me, receives instruction himself without lessening mine; as he who lights his taper at mine, receives light without darkening me.' -TJ
	Submit to Quotes

09-22-2005, 09:32 AM	#2
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	Damn you GD. Now I am not going to be able to get any work done because I will be trying to think of the solution for this. Hopefully somebody will save my day and post a solution soon. __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln
	Submit to Quotes

09-22-2005, 09:43 AM	#3
scaeagles I LIKE! Join Date: Jan 2005 Posts: 7,819	Ditto Kevy.
	Submit to Quotes

09-22-2005, 09:58 AM	#4
scaeagles I LIKE! Join Date: Jan 2005 Posts: 7,819	My solution, if possible. I would add another column C. Column C has the following formula in each cell in the column - =if (a1="yes",b1,"") When copying the formula, of course the cell references change. At the bottom of column C, a simple sum can be used for the range of data. This way, whenever the value of column A changes to or from yes, the data in column C is updated appropriately, and thus the total. Of course, that only works if you can add column C.
	Submit to Quotes

09-22-2005, 09:58 AM	#5
Ponine Virgin Ears Join Date: Jan 2005 Location: San Diego Posts: 2,075	could you create a new column, even possbily hidden that would say (If A=yes, then..) and have it put the positive value in a new column, then add all the values in that column into a cell for your total? __________________ There's something strange, There's something wrong. I see a change - It's like when love dies.
	Submit to Quotes

09-22-2005, 09:59 AM	#6
Ghoulish Delight I Floop the Pig Join Date: Jan 2005 Location: Alternative Swankstyle Posts: 19,348	Yeah, I've thought of that solution. That'll be my fall back, but I'm still hoping for a more elegant solution that doesn't require the extra column __________________ 'He who receives an idea from me, receives instruction himself without lessening mine; as he who lights his taper at mine, receives light without darkening me.' -TJ
	Submit to Quotes

09-22-2005, 10:01 AM	#7
scaeagles I LIKE! Join Date: Jan 2005 Posts: 7,819	Well, you just want everything, don't you?
	Submit to Quotes

09-22-2005, 05:15 PM	#8
Moonliner 8/30/14 - Disneyland -10k or Bust. Join Date: Jan 2005 Posts: 9,022	Hummmm.... If we have A B 1 yes 1 2 null 5 3 yes 2 4 null 7 Then try: =SUMPRODUCT((B1:B4)*(A1:A4="yes")) How's that? Note: I just added "null" to help the spacing. __________________ - Taking it one step at a time.
	Submit to Quotes

09-22-2005, 11:11 PM	#9
Ghoulish Delight I Floop the Pig Join Date: Jan 2005 Location: Alternative Swankstyle Posts: 19,348	Give the man a see-gar, it works! __________________ 'He who receives an idea from me, receives instruction himself without lessening mine; as he who lights his taper at mine, receives light without darkening me.' -TJ
	Submit to Quotes

09-23-2005, 06:52 AM	#10
scaeagles I LIKE! Join Date: Jan 2005 Posts: 7,819	I bow to your Excel-ence, Moonliner.
	Submit to Quotes