|
INDIRECT is what you want, but you can be a little fancier with the math. This formula will give you the row number to stop at for each row your formula is on: INT((ROW()-3)/2)+3 So something like: =MAX($D$3:INDIRECT("D"&(INT((ROW()-3)/2)+3))) will work for your first formula. For the second formula, the math to give you the variable row number is: INT((ROW()-4)/2)+5 So your second column would be: =MAX(INDIRECT("D"&(INT((ROW()-4)/2)+5))  7)Word of warning regarding INDIRECT though: Excel (2003 at least, unsure of 2007) will recalculate it every time it recalculates any cell since it does not know if the formula INDIRECT is pointing to has changed or not. This means when you have lot (thousands) of formulas that Excel may start to operate very slowly with any change due to the massive number of recalculations required.
|
#
¿
Jan 5, 2010 00:16
|
|
|
|
| # ¿ Apr 27, 2024 17:29 |
|
|
It looks like your data is already set up perfectly so all you need to do is select it all, click on Insert, Chart, and change the Chart Type (try right click) to Line.
|
|
#
¿
Nov 15, 2010 15:46
|
|
|
G-Dub posted:You probably want to look at the VLOOKUP function within Excel. I presume there is one address per city? I would have a table of City|Address and then your VLOOKUP would look like: Make sure when using the vlookup that you add the 4th parameter and set it to 0 or false as such: VLOOKUP(<Cell ref of dropdown>,<range of table>,2,0) Leaving it at the default of 1 or TRUE will sometimes return bad results unless you table is sorted and it either has the data you're looking for or you add additional checks to make sure the data is present in the table. Using 0 or FALSE will return an error with what you were searching for was not found.
|
|
#
¿
Dec 10, 2010 06:18
|
|
|
See if there are any uses of the INDIRECT function anywhere. These will cause full recalculations every time anything changes and can often be replaced with other lookup functions. Look for VLOOKUPs that are searching through a lot of data (10k+ records). Excel 2003 is very bad at optimizing these and even a few thousand of them start to really slow down the spreadsheet. If the target data of the vlookup can be guaranteed to be sorted at all times there are some ways to optimize the vlookup with setting the 4th option to true. You'll need some error checking if you do this as well to make sure what you're looking for is in the data. Are there any VLOOKUPs to pivot tables? If so, replace those with GETPIVOTDATA instead. This is more advanced, but you may also look at replacing some of the calculations with pivot table formulas.
|
|
#
¿
Feb 10, 2011 17:28
|
|
|
Do you have a lot of INDIRECT formulas in the 2nd file? I've seen cases where they can slow things down quite a bit. This happens because every time a change happens in file 1 then all of the INDIRECT formulas in file 2 that didn't exist before are now being recalculated constantly.
|
|
#
¿
Apr 4, 2011 22:15
|
|
|
Assuming your example data is in cells A1 to A9, I think this will do it:code:
|
|
#
¿
Apr 11, 2011 02:19
|
|
|
Assuming your data starts in cell A1, put this in B1:code:
|
|
#
¿
Apr 20, 2011 20:13
|
|
|
You can have Excel use External Data Sources and point it to the Access file. If it's a pivot table that's an option for the data source, but you can also just have raw data update into the cells.
|
|
#
¿
May 13, 2011 17:59
|
|
|
esquilax posted:I don't know much about pivot charts, but I do know that pivot tables don't let you compare across value types stored in different columns. Excel will basically never combine or compare 'apples' and 'pears' in a pivot table. The other option is setting up a pivot table formula. If the columns are named apple, pear, and peach then the formula for peach percent would simply be: Peach / (Apple + Pear + Peach). Then instead of putting the 3 value columns in your pivot you put the 3 formulas in your pivot table. They will accurately sum up the totals for each grouping, which in this case is month).
|
|
#
¿
May 16, 2011 16:11
|
|
|
You already found VLOOKUP, but another option is COUNTIF and even better if you have Excel 2007 or newer is COUNTIFS which lets you count based on criteria in multiple columns.
|
|
#
¿
May 24, 2011 15:20
|
|
|
This is exactly what a pivot table is for. Delete row 1 in your data. Click on Cell A1. On the Ribbon bar, click Insert, then PivotTable (far left) Leave it at the default settings and click OK on what popup. You should have a pivot table on a new tab now. On the right in the PivotTable Field List: Drag IndustryColCode to the box named Column Labels. Drag IndustryRowCode to the box named Row Labels. Drag IxIDirectRequirements to the box named Values. You may need to format the data in the pivot table. This can be done by right clicking on the data, selecting Value Field Settings, and then clicking Number Format and setting your appropriate format. Right click in the middle of your pivot table and select PivotTable Options. In the section that says "For empty cells show" type a 0 in the box. You should now have your final table set up as you described. Of note: Sometimes Excel will not recognize that the data in your Values field is a number and will perform a count instead of a sum. To change this you right click on the data and go to the Value Field Settings and select the appropriate summary field type (likely Sum or Max for your data).
|
|
#
¿
Jun 13, 2011 16:47
|
|
|
How hard is it to rearrange your source data? If you can set it up as 4 columns (name,id,item,cost) then it will be easy to get the pivot table very close to what you want. Note, you don't need a row for total in this setup. The pivot table will sum up the individual parts into a total for you. Once the data is in the above format, all you need to do is set the name as your column field and cost as your data field. Then double click on a name and select item and it will be added as an expanded 2nd column with the item level details. To show and hide the details for each name you just double click them. You only need to select the item field the first time and I'm sure there is another way to add it with all of the details collapse by default, but I don't know it without Excel in front of me.
|
|
#
¿
Jul 1, 2011 06:16
|
|
|
If your data starts in cell A1, put this in B1. It will return true for the addresses you want to keep: =not(iserror(find("abcd.net",A1))) Note: This will also find those that have "abcd.net" in the mailbox name as well, but I'm assuming it won't be in your data. With necessary error checking it's a bit more complicated, but this only finds the ones with "abcd.net" after the @ sign: =if(not(iserror(find("@",A1))),not(iserror(find("abcd.net",A1,find("@",A1))))) Aredna fucked around with this message at 20:41 on Jul 6, 2011 |
|
#
¿
Jul 6, 2011 20:39
|
|
|
This should do what you want assuming that you don't want a resource to be used more than once on a single day. I don't have a lot of time to explain how it works right now, but will try to remember to edit this it in later. To use it, set up the formulas as shown and assuming that "Resource" is in Cell A1. Note that the Day formula is an array formula and to enter it rather than pressing ENTER you need to press CTRL-SHIFT-ENTER. Then you need to drag that formula down rather than copy/paste or you'll get an error. After the formulas are in, then sort column B ascending and Day will be allocated to the resources randomly. Also of note: If you have any 0s near the bottom for the day then the greedy allocation method this uses failed due to two of the same resource being left over for the last day. A simple workaround would be just resort column B until you have no Day 0s. To adjust the number of days, change all 3 of the ROW($1:$3) to go to how many every days you need instead of 3 days. To change the number of resources per day, change the <4 to be < (desired resources + 1). Formula so you don't have to type it for C2: code:
|
|
#
¿
Jan 5, 2012 16:58
|
|
|
To explain the logic behind this formula, it helps to first understand how an array formula works. What is happening the way I am using it below is that it is evaluated for each piece of the array. In this case, that means once each for ROW($1), ROW($2), and ROW($3). We are using ROW($x) to represent each possible day. So the formula we have here code:code:This piece of the formula breaks down into 3 pieces: 1) (COUNTIF($C$1:C1,1)<4) 2) (COUNTIFS($A$1:A1,A2,$C$1:C1,1)=0) 3) 1 These logic for these pieces is: 1) Count how many times the day we are checking has already been allocated a resource and verify it is less than 4. If it is more than 4 then we return false (think 0). 2) Count how many times the resource on this row has been allocated to the day we are checking, and validate that the count =0, therefore unused. If it has been used we return false. 3) the day we are checking We then multiply these all together - if either piece 1 or piece 2 evaluate to false, the formula returns 0 rather than the current day. After evaluating this formula for all 3 days we then take the max day returned to find an available and valid day to allocate the resource for this row. I used max here instead of min due to 0 being returned for invalid days. This means our resources are allocated to the days in reverse order, but the end result is essentially the same. I wrote this all up quickly so if anyone has any questions about the logic please feel free to ask for clarification.
|
|
#
¿
Jan 6, 2012 04:37
|
|
|
I would make a default pivot table off of the data (select the columns with your data, Insert, Pivot Table, OK) Put the ID (or Name if it's also unique) as your Row Label Put Selected as your Column Label Put Selected as your values Sort the column under X either descending - anything with more than 1 is at the top, anything with 0 is at the bottom. Since we selected all of the columns in the first step when she wants to refresh this all she needs to do is paste the data on top of what is there currently and then go to the pivot table, right click on it, and select refresh. You could also do something with countifs (2007 or 2010) or an array formula (2003), but it will end up with more complicated steps for her to update it each time meaning it's easier for her to make a mistake and need your support again.
|
|
#
¿
May 25, 2012 16:00
|
|
|
Since it's limited to 4 sets of data I just do it the brute force way and do all of the comparisons, defaulting to TRUE if either cell in a specific comparison is blank. Assuming your table below is from Cells A1:J4, then paste this into I2 (Desc Match for the first row), but on one line instead of 3 as below that I had to do to prevent table breaks code:
|
|
#
¿
Jul 30, 2012 15:50
|
|
|
I think this will do what you need if you put it in BI4 and copy it down: =INDEX($AC$3:$BG$3,0,MATCH(BH4,AC4:BG4,0)) Note: This will have problems if you have hidden values that also produce a match. If that is possible to happen then you'll just need to hard code an ugly nested IF statement or change the way your data is laid out. Aredna fucked around with this message at 00:13 on Aug 22, 2012 |
|
#
¿
Aug 22, 2012 00:10
|
|
|
Assuming your table is set up this: Your formula in cell L5 is: code:
|
|
#
¿
Sep 20, 2012 19:20
|
|
|
Rowe posted:That works for the example I tried, thank you so much. I should have added this in the initial post but if I need to copy the formula down a column for each interest rate, what do I need to change for it to continue to work (if you continued down column L with descending rates)? Right now it only seems to work for the initial one I try. Change any fields it is pointing to that won't be moving to be static references by adding a $ to the fields. I would guess this is what you need, but you may need to make some additional customizations. code:
|
|
#
¿
Sep 20, 2012 19:58
|
|
|
|
| # ¿ Apr 27, 2024 17:29 |
|
|
If you have 2007 or newer look up COUNTIFS and if that is >0 you can put Yes, which should be cleaner than concatenation. Your cell might be an empty string so instead of ISBLANK you may test of it =""
|
|
#
¿
Jul 11, 2013 15:09
|
|