large

[mc-iii]

i have two sheet, one is for the actual month and another last month.


Teh estructure of both sheet is

Sheet_1 - Month N
Code Price
A 10
X 9
Z 6
B 3
R 2
W 5

Sheet_2 - Month N-1
Code Price
H 11
M 9
X 2
A 4
Z 2


Note that the two sheet didn´t have the same number of rows, and i hav
code in this month that no exist last month

I want to know with a fromula (last) which is the code with the bigges
increase of price, and the second one and the 3º.


Thank

 

[Guest]

Ignoreing any codes that existed last month but do not exist this month, you could insert a column after your current month data to lookup last month's data. After that it is a simple matter to calculate the percentage increase.

So on sheet one in a blank column (lets assume column C) Also assume code is in column A on both sheets and cost is in column B on both sheets. The code for cell C2 would be:

=IF(ISNA(VLOOKUP(A2,Sheet2!$A:$B,2,0)),0,VLOOKUP(C2,Sheet2!$A:$B,2,0))

The VLOOKUP() function works like this: It takes the value of C2, which is one of your cost codes, and then it goes to Column A of Sheet2 and runs down looking for an exact match for that code. When it finds that match, it returns the result in column B in the same row as the match.

If it cannot find an exact match it returns #N/A. Since you need to calculate percentage increases, #N/A is unacceptable, so the IF(ISNA()) function tests for that result and replaces it with 0.

In the VLOOKUP() fomula, A2 is the location of the code to be looked up, Sheet2!$A:$B is the reference of the two-column lookup range containing the codes to look up and the cost to be returned. "2" says return the contents of the second column in the lookup range, and FALSE makes the function find an exact match.

Now, if you need to list all codes used in either month including those used last month but not used this month (which would be definition have reduced by 100%), you would probably need a pivot table. If that is the case, post a response in this thread and I will try to help.

Hope this helps.

 

[mc-iii]

thanks, but i can´t do another column on the sheet, so i need a formul
or something like tha

 

[Guest]

OK

=IF(ISNA(VLOOKUP(A2,Sheet2!$A:$B,2,0)),0,IF(VLOOKUP(A2,Sheet2!$A:$B,2,0)=0,0,(B2-VLOOKUP(C2,Sheet2!$A:$B,2,0))/VLOOKUP(A2,Sheet2!$A:$B,2,0)))

Looks pretty horrible to me though. Maybe there is a tidier way of putting it. One possibility would be to create a named formula (e.g. LMCost) for the Lookup function and substitute that in the above. Haven't tried it though, so let me know how you get on.

 

[mc-iii]

it could be the way, and now i need the best three perfomences of th
prices



Thanks a lot


mc-ii

 

[mc-iii]

thanks,

all in the same formula?



mc-iii

 

[mc-iii]

My fault not a good explanation:

I have two sheet, one is for the actual month and another last month.

The estructure of both sheet is

Sheet_1 - Month N
Code Price
A 10
X 9
Z 6
B 3
F 2
W 5
R 11

Sheet_2 - Month N-1
Code Price
H 11
M 9
R 10
X 2
A 4
Z 2


Note that the two sheet didn´t have the same number of rows, and i hav
code in this month that no exist last month

I want to know with a formula (large) which is the code with th
biggest increase of price, and the second one and the 3º.

Result of the formula:
1. Code X - Increase: 350%
2. Code Z - Increase: 200%
3. Code A - Increase: 150%




Thank

 

[Guest]

LARGE can provide the size of each of the first second and third largest increases for you, but you really would be better served with a helper column somewhere. If you aren't able to modify the two sheets themselves, insert a third sheet to hold your helper column.

Sheet3 Would then look like this:

Col B Link to sheet1! Col A
Col C Link to sheet1! Col B
Col D VLOOKUP() function as described earlier to return matching values from Sheet 2 Column B
Col E Simple percentage increase column
Col A RANK() function as described earlier (you wouldn't need to suppress rankings greater than 3, so your formula could be a little simpler)

Your summary showing the three highest increases can then cross reference the helper sheet using a VLOOKUP() function or a combination MATCH() & INDEX() formula to find each of ranks 1,2 & 3 and return the relevant code and percentage increase.

The only hole in this is if you have two or more products with identical percentage increases, these formulae would only provide the first.