Re: Sum row of numbers based on date range for columns
For those discovering this themselves ....
To get this so that it worked when copied down to addition rows I modified the formula as follows:
=SUM(OFFSET(INDEX($I$21:$AM$21,1,MATCH(DAY($AJ$1),$I$21:$AM$21,0)):INDEX($I$21:$AM$21,1,MATCH(DAY($AJ$2),$I$21:$AM$21,0)),ROW(A1),0))
This then uses absolute references for the dates and header rows, but a relative reference for the source row to be SUMed.
Thanks again for this bit of awesome advice.
Re: Sum row of numbers based on date range for columns
That's excellent!!
Thanks !!
Re: IF formula based on MONTH
Try this
=IF((YEAR(TODAY())-YEAR(A1))*12+MONTH(TODAY())-MONTH(A1)>0,"RED",IF((YEAR(TODAY())-YEAR(A1))*12+MONTH(TODAY())-MONTH(A1)=0,"GREEN",IF((YEAR(TODAY())-YEAR(A1))*12+MONTH(TODAY())-MONTH(A1)=-1,"PURPLE","BLUE")))
The determines how many months based on the year *12 + the months in the date .... and while it does over-calculate an extra year it does it consistently.
It then does an IF based on the difference, if TODAY is more months than the date in A1 then RED ... etc.
Re: IF formula based on MONTH
Try this
=IF((MOD(YEAR(A1),100)*100 + MONTH(A1)) < (MOD(YEAR(TODAY()),100)*100 + MONTH(TODAY())),"RED",IF((MOD(YEAR(A1),100)*100 + MONTH(A1)) = (MOD(YEAR(TODAY()),100)*100 + MONTH(TODAY())),"GREEN",IF((MOD(YEAR(A1),100)*100 + MONTH(A1)) = (MOD(YEAR(TODAY()),100)*100 + MONTH(TODAY()))+1,"PURPLE","BLUE")))
Guys I am stuck with a certain format of a document but I want to be able to sum up the value in particular rows - this formula would then be replicated down the sheet as more data is entered.
The image below shows the structure so let me explain the key areas:
The effective date range for the data is shown at AJ1 & AJ2 - These are date fields formatted to display as they are. These also cause the "grey-out" effect in Cols W:AM
The data I wish to sum is Row22 - but only in the displayed columns (based on the dates at the top)
The result would be in cell AN27.
So the first date (1st of the month) always starts in Col I (9) and the last is in Col AM (39).
[Blocked Image: http://www.ozgrid.com/forum/attachment.php?attachmentid=65204&d=1431473222]
My theory was that I could use a sum for COL(8) + Day(start date) to COL (8) + Day(finish date)
Something like ... =SUM( Col(8)+Day(startDate) Row(22) : Col(8)+Day(endDate) Row(22) )
Translates to ... =SUM(Z22:AD22)
Just not sure how to write this as a formula - or do I need to use a UDF - this thing has a mile of VBA behind it so one more bit of code won't make a difference.
FWIW we are trapped in Excel 2003 as well!
Re: two direction lookup (with a twist)
Awesome - works perfectly - now to name some ranges and make it visually friendly
Hey guys - been a while since I have needed your help but here I am again.
I am trying to work out how to do a 2D lookup based on data similar to below. (I am not tied to the layout if there is a better way to lay this out)
So the data I am trying to achieve is as follows:
I know the person is 44 and they managed to achieve 27 reps - given that they would be at Incentive Level 1 (with the range being from the lowest number to the prior to next range)
If the person was 46 and did 27 reps they would be Incentive Level 2 (next age bracket and higher than the Lvl2 value)
[ATTACH=CONFIG]63681[/ATTACH]
Any idea on how I would write this. I have seen index and match formulas for knowing the x and y axis and finding the intersection but this seems to be the other way slightly.
Re: Sum of first digit if it is a number
Thanks AAE - that's awesome ... I am assuming the +0 forces a text-Number to a Number-Number.
Was working on a UDF, but hadn't even considered that problem.
I really need to get my head around Arrays.
Thanks again!
Hey guys,
I was sure I used to know how to do this but I think age is getting the better of me.
I require a formula that will add a group of values across Columns I:AM - but only the leftmost digit and only if it is a number.
The list of valid values for these cells are shown below:
1,2,3,6,6S,A,L,C,X,Y,V1,V2,V3,V6,6F,6FS,6F1,6F1S,6F2,6F2S,3F,3FS,3F1,3F2,2F,2FS,2F1,2F2
I only want to add the values from the first character of the bolded entries - so 1, 2 3 or 6
I guess what I am looking for is something like IF IS NUM (LEFT, I:AM, 1) THEN SUM (I:AM)
Any hints or tips here would be greatly appreciated - I could do it with 31 helper columns but I don't really want to if I can help it (as that would be a whole heap of dynamic formuls slowing down my sheet)
Re: Cell Value to define VLOOKUP range
Is this the solution I am looking for ??
Hey guys,
is there a simple way to do a vlookup (or any other more appropriate formula) using the value of another cell to define the named range relevant to that lookup.
eg I will have two different ranges for fitness performance levels, each of these will be named "Male" and "Female".
I would like to do a lookup based on a formula as follows:
[f]=vlookup(D4,NAMED RANGE,2,false)[/f]
the Named range would be a value in the same row ie B4 - being Male or Female
so I am hoping for something like
[f] =vlookup(D4,B4.Value,2,false)[/f]
otherwise I could just use an If and do something like:
[f]=if(B4="Male",vlookup(D4,Male,2,false),vlookup(D4,Female,2,false) )[/f]
Re: VLOOKUP with negative values on the range
of course the other alternate is when the value is NEGATIVE look up the positive * -1, otherwise just look up the positive value
[f]=IF(F2<0,VLOOKUP(ABS(F2),Table,3,TRUE)*-1,VLOOKUP(F2,Table,3,TRUE))[/f]
This will also reduce your lookup table by half!!
Re: VLOOKUP with negative values on the range
OK so I wasn't 100% on that info
-0.54 will return the value from -0.6 as this is actually smaller
You will need to offset your negative values by one place so include a 0 to -.02 as your first value for the negatives and all your results move down one row
Re: VLOOKUP with negative values on the range
If you sort your table in ascending order (so the largest negative at the top to the largest positive at the bottom) you can keep your original formula.
When "TRUE" is used it looks for the largest number in the range NOT GREATER than the source number - if your number is -0.54 and your table has -0.5 and -0.6 it will return the value for -0.5
Re: I don't understand a tutor's example regarding the OFFSET function
If you keep watching the video he is using the OFFSET function to define a range of data for using in a validation list. Creating a "Dynamic" droplist to be used anywhere in the workbook that won't need to be modified every time you add a new value to the list in that column.
Keep watching the video.
The name of the lesson was the first hint.
Re: I don't understand a tutor's example regarding the OFFSET function
^^^ Way to make friends and get help - being pissed at an MVP - GOOD PLAN!
Re: Count value if value in list of values
Whoa - forgot to check my email for a few weeks. Whoops
OK so I have removed all passwords from the pages although if they are locked still then CTRL-ALT-V will unlock them (and reveal the lookups sheet)
What I am hoping to update is the formulas on the Parade Summary in columns D:AH
What I am currently counting is any value ( so <>"") but what I really want to count is any value that matches the list in the Lookups Column G - these are currenly the list of valid markings for attendance of the full list in column F. Happy for G to be a dynamically named range.
Hey guys,
I am trying to do up a summary for an attendance sheet that I have.
The problem is I have a number of values that could all qualify as a valid attendance marking and others I need to ignore
I have that list of value in a named range "Present" - Column G on the Lookups Sheet
And I have three sheets I need to add together
The layout of the three attendance sheets is directed so I can't mess with that - and we need uniformity across all the organisations that record attendances.
On the first sheet/tab I have entered some example markings - the "C" markings need to be ignored as they aren't in my list of valid values
The Summary sheet is still counting them as it is currently counting anything <>"".
I guess in short ... how doe I change <>"" to my named range "Present"
I have zipped the workbook as it is 445kb (as a result of some lots of conditional formatting and terrible layout)
Re: IF B1 has a value of "X" then collect the value of B3, store it then add it to ot
In X3 use the following formula
[f]=SUMPRODUCT(--(B1:T1="X"),B3:T3)[/f]
Re: Create a Production Time Recording Excel Sheet
<sarcasm>Lost on this one</sarcasm>
