hnmurugan Posted August 31, 2009 Share Posted August 31, 2009 (edited) Hello All,I run in to a unique situation and requesting your advise / help on below.Cell A1 = AppleCell B1 = if a1="apple", assign this formula, if a1="orange", assign this formula, if a1="Mango", assign this formulaIs it possible to do?The reason for above request is "assign this formula" already have several if conditions and it is very difficult to edit. Currently i am using if condition=true do this else do that (Apple do this, non apple do that) Now i have to check for orange and mango in b1 itselfI have listed all the formulas in a separate sheet and try creating name (define names for a specific cell or range) but it only take the values not the formulas.Any ideas?.Please let me know if you want me to post a sample.Regards Edited August 31, 2009 by hnmurugan Link to comment Share on other sites More sharing options...
jaclaz Posted August 31, 2009 Share Posted August 31, 2009 Use VLOOKUP.Example:http://www.msfn.org/board/excel-formula-request-t99059.htmljaclaz Link to comment Share on other sites More sharing options...
hnmurugan Posted August 31, 2009 Author Share Posted August 31, 2009 Hi Jaclaz,Thanks for your reply. Please refer to attached. I am not so sure if i can use vlookup to do the job. My understanding is vlookup find the value based on the conditions then present the value. My requirement is attached. Please correct me if i am wrong.RegardsHariDemo.pdf Link to comment Share on other sites More sharing options...
jaclaz Posted August 31, 2009 Share Posted August 31, 2009 Hi Jaclaz,Thanks for your reply. Please refer to attached. I am not so sure if i can use vlookup to do the job. My understanding is vlookup find the value based on the conditions then present the value. My requirement is attached. Please correct me if i am wrong.RegardsHariI cannot get much out of a PDF without Line and Row numbers.Can you attach a sample spreadsheet?jaclaz Link to comment Share on other sites More sharing options...
hnmurugan Posted September 1, 2009 Author Share Posted September 1, 2009 (edited) As requested. Can't upload excel, so zip it. Edited September 8, 2009 by hnmurugan Link to comment Share on other sites More sharing options...
jaclaz Posted September 1, 2009 Share Posted September 1, 2009 See if this is what you need.I had to add an "OT 3.00" and "OT 4.00" to have totals correct, if I understood the reasons for the sheet.The example is "expandable", and it can be bettered, but I kept it as simple as possible in order to give "inspiration" to you.jaclazDemo_revised.zip Link to comment Share on other sites More sharing options...
hnmurugan Posted September 1, 2009 Author Share Posted September 1, 2009 (edited) Thanks for your effort. I admit that you have approached the problem differently by listing the possible values in a table then do a lookup . My idea was to look up for the formula and perform the calculation. Your idea looks interesting and i will give it a try and post the results. However i have attached the actual worksheet for you to have a look. Edited September 8, 2009 by hnmurugan Link to comment Share on other sites More sharing options...
jaclaz Posted September 1, 2009 Share Posted September 1, 2009 OT, but not much, you don't normally need the VLOOKUP to assign the "textual" day of the week.You can format the cell as "ddd" and link it to a date allright, then use WEEKDAY() on it to have the 1 to 7 value that you may need to choose among different formulas.jaclaz Link to comment Share on other sites More sharing options...
hnmurugan Posted September 8, 2009 Author Share Posted September 8, 2009 Not meant for show case. It is to enable you with more info to have a clear understanding. I have tried your solution partially (without implementing the vlookup). refer to attached.regardsHariManhrs.zip Link to comment Share on other sites More sharing options...
jaclaz Posted September 8, 2009 Share Posted September 8, 2009 (edited) Not meant for show case. It is to enable you with more info to have a clear understanding. I have tried your solution partially (without implementing the vlookup). refer to attached.regardsHariI am not sure to have any clearer understanding than I had before. Unless I am grossly mistaken the attachment contains only a bunch of Excel sheets with NO formulas, but values. What I am I supposed to do with it?Printing it and attaching it to the wall? Nice colours, BTW. jaclaz Edited September 8, 2009 by jaclaz Link to comment Share on other sites More sharing options...
hnmurugan Posted September 9, 2009 Author Share Posted September 9, 2009 Jaclaz - I am not sure to have any clearer understanding than I had beforeHari - Honestly i did not explain clearly or you did not understand clearly. Jaclaz - Unless I am grossly mistaken the attachment contains only a bunch of Excel sheets with NO formulas, but valuesHari - It was partially implemented thus no formulas, My initial request is to to deal with the formulas, Your reply is to deal with values. QuoteThanks for your effort. I admit that you have approached the problem differently by listing the possible values in a table then do a lookup . My idea was to look up for the formula and perform the calculation. Your idea looks interesting and i will give it a try and post the results. UnquoteJaclaz - What I am I supposed to do with it?Hari - Can you please help when i am stuck while trying your suggestion.Ok... Let me request in a much clearer way.Disregard all except demo_revised.xls and consider the two work sheetsSheet Vlookup_Example - take the total hrs, check the designation and lookup for the value in tables sheet and list the value + (check must be 0)I think understand this clearlySheet Tables - list all the values for each designationDesignation 1 - Normal =a, Ot1.5 =bDesignation 2 - Normal =c, Ot1.5 =dWhere we tried it for example.a,b = 24 (total hrs)c,d = 24 (total hrs)Is my understanding clear?I am testing it as below example where initially the break deduction was not highlighted.Example:7:30 to 18:30, then total hrs =11If designation 1 weekdayAdd /assign 8hrs to basic, deduct 1hrs for break, Add /assign 2hrs to OT1.5If designation 1 satAdd /assign 4hrs to basic, deduct 1hrs for break, Add /assign 6hrs to OT1.5If designation 1 SundayAdd /assign 0hrs to basic, deduct 1hrs for break, Add /assign 10hrs to OT2.0Since i believe you expertise (to guide or inspire not doing the job for me) can offer solution much quicker than me, can the demo_revised.xls accommodate this situation?if not can i use this?http://www.spreadsheet123.com/excel_tut/ex.../if_custom.htmlregardsHari Link to comment Share on other sites More sharing options...
jaclaz Posted September 9, 2009 Share Posted September 9, 2009 (edited) I am completely failing to understand the question.Sheet Tables - list all the values for each designationDesignation 1 - Normal =a, Ot1.5 =bDesignation 2 - Normal =c, Ot1.5 =dWhere we tried it for example.a,b = 24 (total hrs)c,d = 24 (total hrs)Is my understanding clear?I am testing it as below example where initially the break deduction was not highlighted.I don't understand.We don't have a or b, nor c or d, we have:DriverTechWe don't have a single "24" in all the sheet, we never talked about break deduction.This is more understandable (maybe):Example:7:30 to 18:30, then total hrs =11If designation 1 weekdayAdd /assign 8hrs to basic, deduct 1hrs for break, Add /assign 2hrs to OT1.5If designation 1 satAdd /assign 4hrs to basic, deduct 1hrs for break, Add /assign 6hrs to OT1.5If designation 1 SundayAdd /assign 0hrs to basic, deduct 1hrs for break, Add /assign 10hrs to OT2.0As I understand it:Designation 1 (Driver?) is payed:on weekdays up to 8 hours "standard" rate, everything above 8 hrs as "overtime" (conventionally OT 1.5)on saturdays up to 4 hours "standard" rate, everything above 4 hrs as "overtime" (conventionally OT 1.5)on sundays and public holidays anything is payed as "holiday overtime" (conventionally OT 2.0)The deduction for break does not makes sense to me, at least in this case:If designation 1 SundayAdd /assign 0hrs to basic, deduct 1hrs for break, Add /assign 10hrs to OT2.0This would mean that if the driver works 2 hours on sunday morning, from, say, 10.00 to 12.00 he is payed one hour only. Isn't there a limit to the total amount of hours per day?Is it 10 hours per day?Or there is an "overtime beyond limit per day" (conventionally OT 3.0)?Isn't the break deduction tied to a minimum amount of hours worked in the given day?Or there is a minimum of worked hours per day (say 7 hours per day minimum)? As I visualize the sheet, the "main" condition is if the given amount of hours have been worked in either of three cases:workdaysaturdaysunday or holidayonce decided which day is it, the Vlookup simply searches in the corresponding designation table what should be paid.I.E., and with reference to the "Demo_revised.xls" :in sheet VLOOKUP_example you insert between Columns B and C whatever formulas/methods to transform the "TIme in" and "Time Out" in "Total Hours" AND decide whether it is weekday, saturday or sunday.Thus the total hours in Column C become the result of the above.In the "Normal" and following columns you EITHER:make a VLOOKUP to three different tables, i.e. instead of a sheet called "Tables" to three sheets called "Table_workdays", "Table_saturdays", "Table_holydays" Or you make a "cumulative condition".Example:Say that "designation" equals to "Driver" and that the given day is a "weekday", you "concatenate" the condition, i.e. the lookup value becomes either:Driver_weekdayDriver_saturdayDriver_sundaythis way you have a single "Tables" sheet.Give me a few minutes, and I'll post an example of the above.jaclaz Edited September 9, 2009 by jaclaz Link to comment Share on other sites More sharing options...
jaclaz Posted September 9, 2009 Share Posted September 9, 2009 See if this is understandable.jaclazDemo_revised_2.zip Link to comment Share on other sites More sharing options...
hnmurugan Posted September 14, 2009 Author Share Posted September 14, 2009 (edited) I am sorry to say this. I have not provided the requirements clearly. Thus i will treat Demo_revised_2.xls as stage 2 and hold it. Please consider / refer to your Demo_revised.xls with attached and please clarify with me if something is not clear before try anything.Requirements.zip Edited September 15, 2009 by hnmurugan Link to comment Share on other sites More sharing options...
jaclaz Posted September 15, 2009 Share Posted September 15, 2009 .xlsx?.docx?Bad, bad formats. Can I have them in plain .xls and .doc?jaclaz Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now