confusing formula

[deuce]

Hi,

I have a list of different names/values in multiples of -n, however I find that the IF formula which I created is applying formulas incorrectly. (if(and(b3<b2, b3<b4), Column A1, 0)

I'll use 3 occurences of unique names as an example: Column A : Mike, Mike, Mike, John, John, John, Jane, Jane, Jane, Andrew, Andrew, Andrew, Peter, Peter, Peter, Jason, Jason, Jason.

In Column B: 1,1,1,2,2,2,3,3,3,4,4,4,5,5,5,6,6,6,7,7,7,8,8,8,9,9,9,10,10,10.

The trouble arises when I drag the formula down, and I'm exhausted working on this.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you please explain what you are trying to do and what the outcome should be?

 

[deuce]

hi

07' in the office otherwise libre. hope this helps.

 

[deuce]

The outcome required is that the formula should calculate based on the name of the person and output that result if condition is true.

 

[Fluff]

Is this what you want?

+Fluff 1.xlsm
	A	B
1
2	Mike	1
3	Mike	1
4	Mike	1
5	John	2
6	John	2
7	John	2
8	Jane	3
9	Jane	3
10	Jane	3
11	Andrew	4
12	Andrew	4
13	Andrew	4
Master

Cell Formulas
Range	Formula
B2:B13	B2	=IF(A2<>A1,N(B1)+1,B1)

 

[deuce]

When the formula i posted is applied to cells then everything is okay. when the formula is dragged down past that then the formula gives the wrong results probably due to name/formula overlap(?).

fluff i don't understand how that formula works.

 

[Fluff]

Can you please explain exactly what you are trying to do & post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[deuce]

to better explain.

in A2,A3,A4 you have names
in D2,D3,D4 & E2,E3,E4 have random figures
in F2 input the formula i posted initially and drag it down
on the 4th cell you'd notice the formula referring to the cell above it, and in order for me to get the correct result I have to modify references in the 4th cell and drag them down 2 rows then again change the formula references/output references each time there is a name change. when the name changes that's when the formula goes haywire.

 

[Fluff]

That does not tell me what you are trying to do, nor does it show me what your expected results are.

 

[deuce]

fluff, the formula posted by you is not giving the required results. it has to calculate in batches of 3, when there is a name change in column A it would be applicable to those 3 cells and not any above that changed name in column A.

so for example the formula for john john john has to change and be applicable to andrew andrew andrew and not john andrew andrew(wrong results).

hope this helps.