jack_package_200
Banned
Edited Out
Don't respond to this thread anymore
Don't respond to this thread anymore
-
Hey, guest user. Hope you're enjoying NeoGAF! Have you considered registering for an account? Come join us and add your take to the daily discourse.
Need some help with Microsoft Excel and GAF is usually very nice
- Thread starter jack_package_200
- Start date
- Status
Impossible to say without seeing your cell data.
anonymous_abc
Member
I second this.
But irrespective of that Office is kinda weird in regards to x-axis tick placement.
Prophet Steve
Member
Click on your graph, and I think the third option on the right that pops up is the one you need "Chart Filters". Bottom right you have "select data". There you have Switch Row/Column or you can do what you need to do.
jack_package_200
Banned
I know you guys couldn't help but I just discovered what I missed in the instructions.
Thanks anyway
Thanks anyway
Sorry for the thread bump but it's only a few days old and wasn't sure where else I should put this!
Having trouble with a problem in work and I'm hoping a GAF Excel wizard can suggest a solution and teach me something new in the process. They're making changes to the pension scheme here and existing members have a length of protection on their current conditions, dictated by their date of birth:
I've been asked if I can knock something up that would look at a column of dates of birth and return the date each employee's protection is due to end. The only way I can think to do it is with nested IF-ELSE functions but since there are 48 date ranges that could get really fiddly and it would be easy to mess up just one of the 48. Is there some other method I could use do this that I'm overlooking?
Edit: something along the lines of IF date of birth is greater than A AND less than B then C. No idea if/how you can do something like that in Excel though?
Having trouble with a problem in work and I'm hoping a GAF Excel wizard can suggest a solution and teach me something new in the process. They're making changes to the pension scheme here and existing members have a length of protection on their current conditions, dictated by their date of birth:
I've been asked if I can knock something up that would look at a column of dates of birth and return the date each employee's protection is due to end. The only way I can think to do it is with nested IF-ELSE functions but since there are 48 date ranges that could get really fiddly and it would be easy to mess up just one of the 48. Is there some other method I could use do this that I'm overlooking?
Edit: something along the lines of IF date of birth is greater than A AND less than B then C. No idea if/how you can do something like that in Excel though?
I'm pretty sure Vlookup can help you with that.
I don't remember exactly which values you need to put it in, but it's fairly simple and you can look it up in the help section of Excel.
It basically looks up a specific value within the first colloumn (sp) of the array, and then retrieve the a value from the same array. Just drag the formula to the very end, and it should work.
Unless I misunderstood what you need...
I don't remember exactly which values you need to put it in, but it's fairly simple and you can look it up in the help section of Excel.
It basically looks up a specific value within the first colloumn (sp) of the array, and then retrieve the a value from the same array. Just drag the formula to the very end, and it should work.
Unless I misunderstood what you need...
VLOOKUP worked. Thanks very much! I didn't think of using that because not every date would be in the table for it to "lookup" but it appears to essentially "round down" and if a date isn't in the table it returns the result from the previous date that is in the table. In this scenario that's exactly what I'm after
=INDEX(A1:C6,MATCH(E2,A1:A6,1),3)
Index/Match imho is much better then V/H/Lookup.. works much the same way, but when using VLookup/HLookup you are utilsing the first column/row as the 'Lookup Value'
So, given your example dataset, and assuming that the 'Lookup Value' is in cell E2, deconstruct the formula as:
=INDEX(A1:C6, Match Formulae ,3)
INDEX( Input Array, Row Number, Column Number )
Row Number is being populated by the Match formula, thus dynamic. (You can do the same with Column number if you want as well)
MATCH(E2,A1:A6,1)
MATCH( Lookup Value, Lookup Array, Match Type )
Match Type
-1 : Less Than
0 : Exact Match
1 : Greater Than
Make sense?
Index/Match imho is much better then V/H/Lookup.. works much the same way, but when using VLookup/HLookup you are utilsing the first column/row as the 'Lookup Value'
So, given your example dataset, and assuming that the 'Lookup Value' is in cell E2, deconstruct the formula as:
=INDEX(A1:C6, Match Formulae ,3)
INDEX( Input Array, Row Number, Column Number )
Row Number is being populated by the Match formula, thus dynamic. (You can do the same with Column number if you want as well)
MATCH(E2,A1:A6,1)
MATCH( Lookup Value, Lookup Array, Match Type )
Match Type
-1 : Less Than
0 : Exact Match
1 : Greater Than
Make sense?
I got something working:
to the right of each row on your table put
=IF(AND($A$1>=A3,$A$1<=E3),1,0) where A1 is your input date and A3 is the lower bound and E3 is the upper bound.
This will then be a column with 0s and a 1 wherever the input date lies inbetween the ranges.
Then just do an index match on the "Date of End Day" column to return the end date.
Hopefully that makes sense. If not let me know and Ill send you a working version
edit: ah evidently Vlookup works for this too
to the right of each row on your table put
=IF(AND($A$1>=A3,$A$1<=E3),1,0) where A1 is your input date and A3 is the lower bound and E3 is the upper bound.
This will then be a column with 0s and a 1 wherever the input date lies inbetween the ranges.
Then just do an index match on the "Date of End Day" column to return the end date.
Hopefully that makes sense. If not let me know and Ill send you a working version
edit: ah evidently Vlookup works for this too
Thanks for all the advice guys!
Just tried this and it works as well. What are the advantages (if any) to using this over VLOOKUP/HLOOKUP? It's not a function I've used before but it's always useful to know these things!
Just tried this and it works as well. What are the advantages (if any) to using this over VLOOKUP/HLOOKUP? It's not a function I've used before but it's always useful to know these things!
It means that you don't need to have the lookup value on the left side. It's more flexible.
When using VLookup / HLookup you are utilsing the first column/row as the 'Lookup Value', thus you can only search the array where you your "search" values are in the first column/row and return values below / to the right, not always ideal, or how your data set is structured, plus you can implement data set error handling.
Using Index will return any value from an array, regardless of its column or row.
Try putting random values in cells A1:C3, apart from cell B2 as myValue
Then use the formula =INDEX(A1:C3,2,2)
So it will return the value from the array, at Row 2, Column 2, in this case whatever is in cell B2, i.e. myValue
You can then seed the Row and Column values with a Match function, use in place of the Row value to replicate VLookup, in place of the Column value to replicate HLookup.
The Match formula will then search the row (or column) for the value, either less than, exact, or greater then.
Put the formula MATCH("myValue",B1:B3,0) in place of the Row value in your Index formula, so find "myValue" in range B1:B3, where its an exact match.
Then change the column search value, so you should have something like this: =INDEX(A1:C3,MATCH("myValue",B1:B3,0),1)
This will return the first column from the array, even though you are searching on the second column... the last numeric is your column to return.
Ttry doing that with a VLookup*
* Before anyone chips in, yes there are ways and means... but Index/Match is a lot more powerful and simpler solution.
Friend of Sonic
Member
I'll cosignon Index Match. I haven't used Vlookup in a while because of how superior index matching is.
- Status