Mr Excel & excelisfun Trick 23: Excel 2010 New Functions

Hi,
I saw EXCELISFUN TRICK 369. I need to do something similar. I tried to follow his code and couldn't. Then I tried copying it and growing his records and still couldn't get it to work.

What I am trying to do is on the first list use an inventory list. Which could be about 2000 - 5000 records or maybe double that (not sure how big of a list I could use in Excel). But lets say it is the max number (if someone could tell me that number I would be most appreciative).

I then will load a second list, or would load a list into second column. I then want the difference (what is missing) from the second list to appear in the third list. If it can give me the row of where it is in the first list that would be great (not a problem if you can't). I just don't know why the code from that video is not working any help would be greatly aprreciated?

Thanks,

Peter Fraga
(fragapete@hotmail.com)

Hi All,

I was setting up a spreadsheet that was based on the following vid:
http://www.youtube.com/user/ExcelIsF...14/tqCEY5YMyqw

Dynamic sub tables based on a master sheet array formula

The formula in question is:
=IF(ROWS(A$7:A7)>$E$1,"",INDEX('2010Corn'!A$4:A$17,SMALL(IF(Table2[From]=$B$1,ROW(Table2[From])-ROW('2010Corn'!$H$4)+1),ROWS(A$7:A7))))

B1 is the customer I'm looking for, E1 is the count for the customer and the master page is 2010Corn. I have 20 sheets looking to this master page for data. It works great, except for an issue when adding a new line in the master table.

What is happening is when I get to the end of a row, I tab to enter a new line in the table. It takes up to a minute for the cell to change color and for me to regain control of the computer.

I have run a performance trace and while the computer is locked, one of the CPU core's is pegged for the whole time with a processor que of up to 10 items at a time.

My question is...does anyone have any hints how to optimize this formula?

Thanks
Tony

Just This Video For Fun

http://www.youtube.com/watch?v=0VvjrldlqI0

I posted this in the Windows 7 forums, but have not seen anything in 10 hours. Please forgive the double post, but I need to find out what the heck is happening-

I just opened an Office 2007 Excel project in 2010 which contains significant VBA. The compiler is puking at the Left function - Compile error - Can't find object or library

I have tried retyping the line and also tried using the Right function, just to see what happened - and that was not accepted either!

Earlier the compiler rejected the Ltrim function also.

ANY ideas?

REAL worried at what else I am now going to run into, now that I "upgraded"...

Great thanks for your thoughts, I have a feeling I am going to have many more questions due to the nature of the problems and upgrading...

And where the heck did my line and position numbers/indicator go in the 2010 VBE????

Hi

Just wondering how man nested IF functions excel 2010 can handle in one cell.

Doug

Hello,

I am confused with Percentile and PercentileRank in excel 2010. I have the following data and what i am looking for is to Know the relative percentiles of each individual score .


Also i am confsued whether i should sort the data in ascending or descending order?

SCORE 73.75 26.09 23.31 11.58 9.07 8.62 8.26 8.21 7.94 7.21 6.79 6.79 6.75 6.12 6.10 5.84 5.47 4.74 4.70 4.30 4.20 4.19 3.76 3.68 3.65 3.57 3.50 3.49 3.36 3.35 3.33 3.30 3.25 3.15 3.10 3.09 3.06 2.95 2.82 2.73 2.68 2.65 2.45 2.43 2.42 2.42 2.38 2.24 2.22 2.12 2.11 2.04 2.04 1.90 1.76 1.68 1.66 1.61 1.54 1.54 1.51 1.48 1.43 1.41 1.38 1.36 1.20 1.10 1.10 1.08 1.07 1.05 0.95 0.94 0.94 0.86 0.83 0.82 0.78 0.78 0.77 0.72 0.72 0.71 0.68 0.65 0.60 0.57 0.56 0.56 0.56 0.55 0.55 0.49 0.45 0.33 0.33 0.16 0.10 0.10
Thank u

If I have two random data sets of equal size (same number of elements) such as set X = (4,7,2,6,...) and set Y = (5,9,3,5,...) is there a way to use excel to find a transfer function between Y and X such that H(x)X(x) = Y(x)? In this case H(x) would be the transfer function which when multiplied by X(x) always produces Y(x) (i.e. X(1)H(1) = Y(1) = 5).

I know I could probably do this with MATLAB or some equivalent software, but I would ultimately have to port the transfer function over to Excel for my application thus it would be nice if I could just do this in Excel.

If I have two random data sets of equal size (same number of elements) such as set X = (4,7,2,6,...) and set Y = (5,9,3,5,...) is there a way to use excel to find a transfer function between Y and X such that H(x)X(x) = Y(x)? In this case H(x) would be the transfer function which when multiplied by X(x) always produces Y(x) (i.e. X(1)H(1) = Y(1) = 5).

I know I could probably do this with MATLAB or some equivalent software, but I would ultimately have to port the transfer function over to Excel for my application thus it would be nice if I could just do this in Excel.

cross posts of this in other forums:
http://www.mrexcel.com/forum/showthr...=1#post2725426
http://www.mathhelpforum.com/math-he...er-181360.html

Good morning,

trying to find out what to do with my spreadsheet and the formulas in it.

If you look into the file 369. I can get to the stage where it outlines the mismatch figure. In the example it is 6 Mismatches.

But I cannot seem to get the table belwo right. Everytime I try and copy past the formula, the first set of data is the one from the first line and then I get a #NUM error, due to the fact that there seems to be a number reoccurence somewhere.

I took the youtube tutorial, but even copying that formula did not work.

My table consist of 2 sets of 600 values each.

Can someone tell me why I am getting this error and help me resolve the issue that the result is an existing set of data?

THANK YOU

BayerStars23
[IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot.png[/IMG][IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot-1.png[/IMG]

hi i have writtern the following in a comman button

sub trythis()
dim x as range
set x = range("b1:b20")
for each x in x
if x.text = "June" then
x.copy
end if
next x
end sub

I have in the range names of the month repeated randomly. June appears 6 times in the list

By executing the above code the last cell containing "June" gets into copy mode (the cell is filled with marchings ants effect). Although the loop goes through many cells containing "June" only the last cell containing "June" shows this effect, how extend this effect to all the cells containing the text "June".