Homer Posted May 22, 2007 Share Posted May 22, 2007 I have a spreadsheet where I'm calculating resolution times by priority, I would like to have a formula which tells me if a target resulting time has been met. e.g. If the priority(column A1) is medium, the resolution time (Column B1) is x.xx hours, the target resolution time 10 hours (column C1) the formula returns a Yes or No value into column F1 There are 3 priorities possible, each with their own resolution time, e.g Medium = 10 hours High = 2 hours Urgent = 0.5 hours Any ideas? Link to comment Share on other sites More sharing options...
couv3z Posted May 22, 2007 Share Posted May 22, 2007 i'm trying to have a bash at this for you, so you want a formula to say yes or no if you meet the time required Link to comment Share on other sites More sharing options...
Homer Posted May 22, 2007 Author Share Posted May 22, 2007 i'm trying to have a bash at this for you, so you want a formula to say yes or no if you meet the time required Yes, thats correct. Thanks matey Link to comment Share on other sites More sharing options...
letmeshowyou Posted May 22, 2007 Share Posted May 22, 2007 am I missing something from your explanation matey? does the layout have to be how you've set it up? the simplest way I can see (without messing about nesting lots of different functions) is to set up a seperate column that calculates response time based on the entry to column A (an IF nest would do this) and then another IF in your target met column confirming if the time taken is lower than the target. Link to comment Share on other sites More sharing options...
couv3z Posted May 22, 2007 Share Posted May 22, 2007 I had to move things a little but heres a easy way to do it and if at any time you can change the target time and it will auto funk all results if it has to be the same layout let me know i'll have another bash Link to comment Share on other sites More sharing options...
Homer Posted May 22, 2007 Author Share Posted May 22, 2007 Thanks Adi, but: I had to move things a little but heres a easy way to do it and if at any time you can change the target time and it will auto funk all results if it has to be the same layout let me know i'll have another bash Excellent, cheers mate thats perfect Didn't think of doing it like that! I suck at Excel Link to comment Share on other sites More sharing options...
letmeshowyou Posted May 22, 2007 Share Posted May 22, 2007 haha pretty much like I'd just done it (but I didn't have the look up table) Link to comment Share on other sites More sharing options...
couv3z Posted May 22, 2007 Share Posted May 22, 2007 anytime Link to comment Share on other sites More sharing options...
BASHTHEBISHOP Posted May 22, 2007 Share Posted May 22, 2007 if it has to be the same layout let me know i'll have another bash Nearly right but u missed the '=' out as in: If target is medium & the resolve time is exactly 10 hours your table returns a 'No' instead of a 'Yes' As in target would of been met. Sorry Link to comment Share on other sites More sharing options...
couv3z Posted May 22, 2007 Share Posted May 22, 2007 your right, but there is a symbol for this but i couldn't find it. it looks like if not this would work =if(B12=C12,"Yes",IF(B12 i think Link to comment Share on other sites More sharing options...
Jake Posted May 22, 2007 Share Posted May 22, 2007 This is what you want Darryl: =IF(OR(AND(A2="Medium",B2 See attached Link to comment Share on other sites More sharing options...
BASHTHEBISHOP Posted May 22, 2007 Share Posted May 22, 2007 This is what you want Darryl: =IF(OR(AND(A2="Medium",B2 See attached Sorry Jake - You missed the 'Urgent'. Otherwise it was perfect Here goes:- =IF(OR(AND(A6="Medium",B6 Link to comment Share on other sites More sharing options...
Jake Posted May 22, 2007 Share Posted May 22, 2007 Whoops, I didn't change the second "Medium" to "Urgent" Well spotted Link to comment Share on other sites More sharing options...
Homer Posted May 22, 2007 Author Share Posted May 22, 2007 Cheers Jake, that helps too Link to comment Share on other sites More sharing options...
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now