Formula That Selects Next Cell

[jmeyer180]

I'm using Excel 2011 on my Mac, and I need to find a way to make excel do the following in a large amount of data:

Put into words, if column 6 doesn't equal the total, then the input needs to be the next cell to the right. Sorry this is the best way I know how to explain it, please let me know how to fix it!

Thanks,

Joel

	1	2	3	4	5	6	7
A				Total	First nonblank entry	Formula I need to figure out
B	3		1	4	3	if(B5=B4,0,if not then the cell after B1 which is B2
C		3		3	3	Should be 0
D	3			3	3	Should be 0
E	5		1	6	5	Should be 0
F	3	2	3	8	3	Should be 2
G			1	1	1	Should be 1

<tbody>
</tbody>

 

[jmeyer180]

what the formula does is compare P2 and Q2, if P2=Q2, then 0, if not then it copies over the cell to the right of M2 which is N2. But since N2 is empty, you get 0

I just applied that to my spreadsheet and copied down and across. The only problem is that it causes some of the cells in the Month 1 column to hit 0. There should be a number greater than 0 in each cell under Month 1 because that is the first number in each row.

 

[ttdk1]

does the Month 1 column correspond to the month of march in column M?

 

[Aladin Akyurek]

If S7=8, then T7=0. If that is true, then U7=6.

In the sense of a formula for cell T7, if(S7=Q7,0,then a formula that I can drag right O7)

	M	N	O	P	Q	R	S	T	U
1	March	April	May	June	Grand Total		Month 1	Month 2	Month 3
2	2		1		3		2
3				1	1		1
4	1			1	2		1
5				1	1		1
6				3	1		3
7		8		6	14		8
8
9

<TBODY>
</TBODY>

I think the headers Month 1, Month 2, and Month 3 are confusing...

Would you just stipulate the actual values that you expect in column S, column T, and column U without any reference to a possible Excel formula?

 

[jmeyer180]

Not quite, think of March, April, May, and June as its own data table. Then we use that information for Month 1.

Maybe the real scenario will help. Each row represents a user on a website. For example, the 1-9 going down the left side of the table I wrote would be 9 different users. Each user starts using the website in a different month. If User 1 buys 1 thing in May, he gets a "1" in May.

From this data, I made Month 1 show the number of times that the user purchased the very first month they joined. So Month 1 shows each user's first month.

So Month 2 should show the second month they were with the website. If they didn't buy anything, they get a "0".

 

[jmeyer180]

To answer you Aladin, I have over 2 excel sheets full of values for this. I won't be able to just plug the numbers in unfortunately, which is why I need find a formula.

 

[Aladin Akyurek]

Not quite, think of March, April, May, and June as its own data table. Then we use that information for Month 1.

Maybe the real scenario will help. Each row represents a user on a website. For example, the 1-9 going down the left side of the table I wrote would be 9 different users. Each user starts using the website in a different month. If User 1 buys 1 thing in May, he gets a "1" in May.

From this data, I made Month 1 show the number of times that the user purchased the very first month they joined. So Month 1 shows each user's first month.

So Month 2 should show the second month they were with the website. If they didn't buy anything, they get a "0".

Filling in the actual values that you expect supports understanding (makes explicit what appears obvious to you)...

S2, control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($M2:$P2,SMALL(IF(ISNUMBER($M2:$P2),COLUMN($M2:$P2)-COLUMN($M2)+1),COLUMNS($S2:S2))),0)

 

[jmeyer180]

Yeah it's tough to put into words. However I copied the formula into my excel file, then changed the ranges accordingly, but it says "not enough arguments were entered into the function".

If easier, I could send you a private message with my email address and I could email you the excel file.

 

[jmeyer180]

Ok I got the formula to work. However I already knew the values from S2:S9. I used a similar equation to find those values. The only problem with the formula, is that it finds all nonblank values. For example, T8 shows "6", but I want it to show 0 because they didn't buy anything in their second month.

 

[Aladin Akyurek]

Yeah it's tough to put into words. However I copied the formula into my excel file, then changed the ranges accordingly, but it says "not enough arguments were entered into the function".

Control+shift+enter is command+return on a Mac system.

If easier, I could send you a private message with my email address and I could email you the excel file.

See the following workbook that implements the suggested formula:

https://dl.dropboxusercontent.com/u/65698317/jmeyer180%20first%20three%20values.xlsx

 

[ttdk1]

I tried aladin's snipper and think it did the job. Fits the explanation

From this data, I made Month 1 show the number of times that the user purchased the very first month they joined. So Month 1 shows each user's first month.

So Month 2 should show the second month they were with the website. If they didn't buy anything, they get a "0".

but earlier you mentioned

If S7=8, then T7=0. If that is true, then U7=6

confusing. Aladin's snippet put T7=6 and U7=0