Might be best explained like this....
If Greg is healthy he is available to train 30 hours in this week but if hes injured hes only available the 30 hours minus the 5 hours injured (25 hours) and if he quits then his available hours to train a zero.
Greg can fit in to either of these three categories:
Healthy
Injured
Quit
I'm trying to find a formula that will return a value depending on the category Greg is defined by.
The spreadsheet outlines it exactly.
Ta.
Last edited by hermithead; 03-11-2010 at 04:08 PM.
Hi hermit,
I'm guessing if "the spreadsheet outlines it exactly" that you'd have a few responses by now. I looked at the sheet and I can't guess where you want a formula and what it should be calculating. Please either explain it in words here, or in the worksheet say something like "I want cell C3 to show me how many hours.... and it should show this many hours (##) because this cell is "X" and this is "y"..."
Thanks!
Thanks Paul. Yep I've updated the spreadsheet and reattached.
This is the formula I've come up with but it's returning a #Value.
IF(VLOOKUP(A4,C:E,3,0),=F:F,H3),=F:F,H3-B6),=F:F,0)
which should equate to:
Lookup to see if Greg is Healthy if he is return 30 hours but if Vlookup returns Greg as Injured return 30 hours minus 5 hours injured or Vlookup returns Greg as Quit return zero hours.
Cheers
This workbook Available hours2.xls displays the values Im trying to achieve.
Last edited by hermithead; 03-11-2010 at 12:38 PM.
First, make sure all of your entries are the same for the week names. "Week 1" is not the same as "Week1" as you show in column D. (Change those to have spaces just like in A3, A8, A13 and A18 as well as in column G.) Also, in A18 remove anything after "Week 4".
The formula below is checking these cells for matches, and will result in an error if it's not finding true matches. This is an array formula which must be confirmed with CTRL+SHIFT+ENTER, not just ENTER.
Put that in B5 then copy to B10, B15, B20.=LOOKUP(INDEX($E$2:$E$5,MATCH(A4&A3,$C$2:$C$5&$D$2:$D$5,0)),{"Healthy","Injured","Quit"},{1,1,0})*VLOOKUP(A3,$G$3:$H$6,2,0)
Thanks Paul thats one powerful array formula! Is it possible to amend this slightly to calculate without "Week 1", "Week 2" etc in column D and cells A3, A8, A13 and A18 (as removed in Available hours3.xls) ? These were only included for display purposes (sorry a bad case of too much moutain dew and mozzarella!). ( "Week 1", "Week 2" etc in column G is still valid and this provides the overall total available hours per week).
To do so would mean you would need to hard code the specific week number into each formula, so you wouldn't be able to use the same formula for all four cells to get the desired result. (Meaning: how would B5 know that it is supposed to reference Week 1 vs. Week 3? etc..)
Paul I've figured it out. Thank You very much fo your help. Very much appreciated!
The major change I made so B5 knew what it was suppsoed to reference was replacing the Vlookup with a direct cell reference, for example Week 1 = 30 hours which is cell H3, Week 2 = H4 and so on. This list will never change and these are actually calculated from another worksheet, they are set values hence being able to reference directly to them.
Brilliant!
=LOOKUP(INDEX($E$2:$E$5,MATCH(A4&A3,$C$2:$C$5&$E$2:$E$5,0)),{"Healthy","Injured","Quit"},{1,1,0})*H3
I also replaced Week 1, Week 2 etc in cells A3,A8, A9, A13 etc with a category, see below which the array then matched with the name and returned the hours for Week 1.
HTML Code:Healthy Greg Set training hours 30 Hours injured 0 Actual training hours 30 Healthy Greg Set training hours 30 Hours injured 0 Actual training hours 30 Injured Greg Set training hours 30 Hours injured 5 Actual training hours 25 Quit Greg Set training hours 0 Hours injured 0 Actual training hours 0
Basically I want this formula to, Lookup a persons name and return text as either "Fixed", "Varied", "None" then if this matches "Fixed" from a list then return a value from a specific cell OR if it matches "Varied" from a list then subtract one value from another OR if it matches "None" from a list then return zero.
=IF(VLOOKUP(A5,Staff!A2:D51,4,0))"=Staff!H2","Paid days!F6"),"=Staff!H3","B12-Paid days!F6"),"=Staff!H4,0"))
Is the syntax correct?
*Note for Paul, Ive decided to change tact the array couldnt account for the Injured example that required Injured hours to be deducted from the Week 1 total. I think my answer lies in the IF and Vlookup formulas combined if I can just sort my syntax. Thanks for your help though.
Found it!
=IF(A6=Staff!H2,'Paid Days'!F6,IF(A6=Staff!H3,B23,IF(A6=Staff!H4,0)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks