[hnmurugan]

Hello All,

I run in to a unique situation and requesting your advise / help on below.

Cell A1 = Apple
Cell B1 = if a1="apple", assign this formula, if a1="orange", assign this formula, if a1="Mango", assign this formula

Is 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 itself

I 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

This post has been edited by hnmurugan: 31 August 2009 - 02:41 AM

[jaclaz]

Use VLOOKUP.
Example:
http://www.msfn.org/...est-t99059.html

jaclaz

[hnmurugan]

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.

Regards
Hari

Attached File(s)

•  Demo.pdf (32.79K)
  Number of downloads: 3

[jaclaz]

hnmurugan, on Aug 31 2009, 11:49 AM, said:

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.

Regards
Hari

I cannot get much out of a PDF without Line and Row numbers.

Can you attach a sample spreadsheet?

jaclaz

[hnmurugan]

As requested. Can't upload excel, so zip it.

This post has been edited by hnmurugan: 08 September 2009 - 12:45 AM

[jaclaz]

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.

jaclaz

Attached File(s)

•  Demo_revised.zip (4.55K)
  Number of downloads: 4

[hnmurugan]

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.

This post has been edited by hnmurugan: 08 September 2009 - 12:49 AM

[jaclaz]

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

[hnmurugan]

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.

regards
Hari

Attached File(s)

•  Manhrs.zip (53.09K)
  Number of downloads: 1

[jaclaz]

hnmurugan, on Sep 8 2009, 08:48 AM, said:

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.

regards
Hari

I 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

This post has been edited by jaclaz: 08 September 2009 - 07:14 AM

[hnmurugan]

Jaclaz - I am not sure to have any clearer understanding than I had before
Hari - 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 values
Hari - It was partially implemented thus no formulas, My initial request is to to deal with the formulas, Your reply is to deal with values.

Quote

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.

Unquote

Jaclaz - 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 sheets

Sheet 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 clearly

Sheet Tables - list all the values for each designation
Designation 1 - Normal =a, Ot1.5 =b
Designation 2 - Normal =c, Ot1.5 =d

Where 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 =11

If designation 1 weekday
Add /assign 8hrs to basic, deduct 1hrs for break, Add /assign 2hrs to OT1.5
If designation 1 sat
Add /assign 4hrs to basic, deduct 1hrs for break, Add /assign 6hrs to OT1.5
If designation 1 Sunday
Add /assign 0hrs to basic, deduct 1hrs for break, Add /assign 10hrs to OT2.0

Since 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.html

regards
Hari

[jaclaz]

I am completely failing to understand the question.

Quote

Sheet Tables - list all the values for each designation
Designation 1 - Normal =a, Ot1.5 =b
Designation 2 - Normal =c, Ot1.5 =d

Where 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:
Driver
Tech

We don't have a single "24" in all the sheet, we never talked about break deduction.


This is more understandable (maybe):

Quote

Example:
7:30 to 18:30, then total hrs =11

If designation 1 weekday
Add /assign 8hrs to basic, deduct 1hrs for break, Add /assign 2hrs to OT1.5
If designation 1 sat
Add /assign 4hrs to basic, deduct 1hrs for break, Add /assign 6hrs to OT1.5
If designation 1 Sunday
Add /assign 0hrs to basic, deduct 1hrs for break, Add /assign 10hrs to OT2.0

As 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:

Quote

If designation 1 Sunday
Add /assign 0hrs to basic, deduct 1hrs for break, Add /assign 10hrs to OT2.0

This 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:

• workday
  
• saturday
  
• sunday or holiday

once 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_weekday
  
• Driver_saturday
  
• Driver_sunday

this way you have a single "Tables" sheet.


Give me a few minutes, and I'll post an example of the above.

jaclaz

This post has been edited by jaclaz: 09 September 2009 - 08:11 AM

[jaclaz]

See if this is understandable.

jaclaz

Attached File(s)

•  Demo_revised_2.zip (9.21K)
  Number of downloads: 2

[hnmurugan]

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.

Attached File(s)

•  Requirements.zip (25.18K)
  Number of downloads: 2

This post has been edited by hnmurugan: 15 September 2009 - 07:08 AM

[jaclaz]

.xlsx?
.docx?


Bad, bad formats.

Can I have them in plain .xls and .doc?

jaclaz

[hnmurugan]

Updated to office 2003 file format. The table structure in requirements.xls may not allow lookup conveniently, thus i have changed in vlookup.xls.  Requirements_2.zip (15.26K)
Number of downloads: 2

Based on vlookup.xls, i have tried as below with no luck.

D3 formula =VLOOKUP(($B3&$C3),Table!$A$2:$E$53,3)
E3 formula =VLOOKUP(($B3&$C3),Table!$A$2:$E$53,4)
F3 formula =VLOOKUP(($B3&$C3),Table!$A$2:$E$53,5)

Any help is greatly appreciated.

Regards
Hari

Attached File(s)

•  Vlookup.zip (5.42K)
  Number of downloads: 3

This post has been edited by hnmurugan: 15 September 2009 - 11:32 PM

[jaclaz]

See if Vlookup2.xls (actually based on "requirements.xls") suites you.

jaclaz

Attached File(s)

•  Vlookup2.zip (5.77K)
  Number of downloads: 1

[hnmurugan]

Please correct me if i am wrong.

Based on quick look on your example Totalhrs is mandatory were as designation is not.

I require the designation as mandatory.

Example: A employee may work as des1 for day 1 and as des4 for day2.

Regards
Hari

[jaclaz]

hnmurugan, on Sep 16 2009, 01:39 PM, said:

Please correct me if i am wrong.

Based on quick look on your example Totalhrs is mandatory were as designation is not.

I require the designation as mandatory.

Example: A employee may work as des1 for day 1 and as des4 for day2.

Regards
Hari

Well, I simply thought that a Driver is a Driver and a Tech is a Tech, and "coupled" "employee#" with Designation.
You can simply overwrite the formulas in colums "Designations" and write the Designation manually or choose it from a list.

jaclaz

[hnmurugan]

jaclaz, on Sep 16 2009, 07:53 PM, said:

hnmurugan, on Sep 16 2009, 01:39 PM, said:

Please correct me if i am wrong.

Based on quick look on your example Totalhrs is mandatory were as designation is not.

I require the designation as mandatory.

Example: A employee may work as des1 for day 1 and as des4 for day2.

Regards
Hari

Well, I simply thought that a Driver is a Driver and a Tech is a Tech, and "coupled" "employee#" with Designation.
You can simply overwrite the formulas in colums "Designations" and write the Designation manually or choose it from a list.

jaclaz

Noted. Thanks for your help.

I figure out why below was not working

D3 formula =VLOOKUP(($B3&$C3),Table!$A$2:$E$53,3)
E3 formula =VLOOKUP(($B3&$C3),Table!$A$2:$E$53,4)
F3 formula =VLOOKUP(($B3&$C3),Table!$A$2:$E$53,5)

Changed as below and changed the designation column value in tables sheet as DES1_4, DES1_8 and so on. It's working now.

D3 formula =VLOOKUP(($B3&"_"&$C3),Table!$A$2:$E$53,3)
E3 formula =VLOOKUP(($B3&"_"&$C3),Table!$A$2:$E$53,4)
F3 formula =VLOOKUP(($B3&"_"&$C3),Table!$A$2:$E$53,5)

Hmm...It is not over yet... Give me some time to test and will post back the results with 1 last requirement.
Once agin thanks fro your help.