-
I teach data analysis using pivot tables in a short course and we usually need to use lookup functions such as VLOOKUP in order to import fields from different data tables into another one before building a pivot table to get the desired analysis results. Sometimes, VLOOKUP is not an appropriate choice...
-
Here is a typical row in the spreadsheet which is nearly 4000 rows of data: Description WBS WBD Material Qty Mea Size | Ball Valve, V600, 02 | C12DO | F1804B | CARBON STEEL, PIPING | 12 | EA | 1" | Im trying to use the "Description" & "WBS" columns for the criteria to match...
-
I am currently putting together a database of timesheets which contain Name, date, hours (plus others, but these are main ones). I need to lookup into a standing data table two criteria, the name and the date. The standing data consists of name, date of rate change and new rate. Therefore I need to find...
-
Hi Steve, Rather than use VBA, I've developed a worksheet only solution, using dynamic named ranges, LINEST linear regression function, datatables, index/match to pick out the best results, and finally solving the simultaneous equations and plotting!! Sorry I don't have time to explain the whole...
-
Have a table of data and I need to interpolate a value. The row names are A,B,C,D and the column names have 0.5, 0.6, 0.7, 0.8 etc. If I want to look up in that table under row B for a column value 0.67, how would I accomplish that? Any ideas!?? Thanks!
-
I have a workbook that uses formulas such as =MATCH(ref,INDIRECT(ref2),0) where ref2 refers to whole columns or whole rows on another open workbook (such as "[test.xls]Sheet1!A:A" or "[test.xls]Sheet1!1:1"). These work perfectly well in Excel2003 but seem to produce error values in...
-
To simplyfie bentleybob's suggestion you can have a look at this add-in: http://www.mrexcel.com/chal/pareto.zip All of the formulas in B are =RAND() functions. Click the checkbox to recalc, and a whole series of dynamic range names cause the chart to redisplay sorted values. It also make it possible...
-
Good afternoon everyone, it's me again. Well, I have another question. This time, I have an answer myself but I would like to hear some recommendations, because I'm not sure I solved this problem in the most elegant way :-) So. The setting is the following. We compare the average sales of last...