Jump to content

Excel 2007 complex formula


hnmurugan

Recommended Posts

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

Edited by hnmurugan
Link to comment
Share on other sites


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

Demo.pdf

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by hnmurugan
Link to comment
Share on other sites

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

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. :unsure:

Unless I am grossly mistaken the attachment contains only a bunch of Excel sheets with NO formulas, but values. :blink:

What I am I supposed to do with it?

Printing it and attaching it to the wall? :whistle:

Nice colours, BTW. :)

jaclaz

Edited by jaclaz
Link to comment
Share on other sites

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

Link to comment
Share on other sites

I am completely failing to understand the question.

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

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:

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. :w00t:

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)? :unsure:

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

Edited by jaclaz
Link to comment
Share on other sites

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 by hnmurugan
Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...