Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Difficult formula required.
I have been told by my excel guru that I cant have the following info in one
formula. Using Excel 2003 Is this true people? Many thanks for thinking about it. X What I am after is; If cell A is "X or Y or Z" & cell B is Mon, Tues, Wed, Thurs or Fri then multiply cell E by £15.00 but If cell A is "X or Y or Z" & cell B is "Sat" then multiply cell E by £20.00 but If cell A is "X or Y or Z" & cell B is "Sun" then multiply cell E by £25.00 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 00:01 & 08:00 (time) then multiply cell E by £10.00 & cell D by £5 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 08:01 & 18:00 (time) then multiply cell E by £12.00 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 18:01 & 23:59 (time) then multiply cell E by £10.00 & cell D by £5. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Difficult formula required.
On Thu, 26 Nov 2009 08:11:01 -0800, Sexy Devil
wrote: I have been told by my excel guru that I cant have the following info in one formula. Using Excel 2003 Is this true people? Many thanks for thinking about it. X What I am after is; If cell A is "X or Y or Z" & cell B is Mon, Tues, Wed, Thurs or Fri then multiply cell E by £15.00 but If cell A is "X or Y or Z" & cell B is "Sat" then multiply cell E by £20.00 but If cell A is "X or Y or Z" & cell B is "Sun" then multiply cell E by £25.00 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 00:01 & 08:00 (time) then multiply cell E by £10.00 & cell D by £5 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 08:01 & 18:00 (time) then multiply cell E by £12.00 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 18:01 & 23:59 (time) then multiply cell E by £10.00 & cell D by £5. It may not be possible because you have not clearly stated your requirements. When you write "multiply cell E by ..." do you want that done within cell E, or are you merely looking for the results of that computation? When you write "multiply cell E by £10.00 & cell D by £5" what, exactly do you mean. Do you want this done within the respective cells, or do you want the sum of these two computations, or do you want the product of these two computations, or do you want something else. If you want to do this with a formula in cell E and cell D, then it is not possible. If you are looking for the results of the computations, then it is possible, depending on what you mean when you are referring to both cells E and D. An example of the first three "IF's" might be: =E2* (AND(OR(A2={"X","Y","Z"}),OR(B2={"Mon","Tues","Wed ","Thurs","Fri"}))*15+ AND(OR(A2={"X","Y","Z"}),B2="Sat")*20+ AND(OR(A2={"X","Y","Z"}),B2="Sun")*25) You can certainly extend this concept to encompass the other conditions, assuming you can answer the above questions. --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Difficult formula required.
Some questions:
#1 - Can column A have anything in it other than V, X, Y and/or Z ? i.e., could it have B or R or something else? #2 - exactly what's in column B? Is it text as "Sat" or "Saturday", or is it actually a date that you've formatted to show the day of week for? I'm not saying I can do this, but knowing this information will make the odds of coming up with a solution better. "Sexy Devil" wrote: I have been told by my excel guru that I cant have the following info in one formula. Using Excel 2003 Is this true people? Many thanks for thinking about it. X What I am after is; If cell A is "X or Y or Z" & cell B is Mon, Tues, Wed, Thurs or Fri then multiply cell E by £15.00 but If cell A is "X or Y or Z" & cell B is "Sat" then multiply cell E by £20.00 but If cell A is "X or Y or Z" & cell B is "Sun" then multiply cell E by £25.00 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 00:01 & 08:00 (time) then multiply cell E by £10.00 & cell D by £5 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 08:01 & 18:00 (time) then multiply cell E by £12.00 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 18:01 & 23:59 (time) then multiply cell E by £10.00 & cell D by £5. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Difficult formula required.
Hi, thanks for taking the time to look
Column A will only have X, Y, Z or V Column B will be just the day Mon, Tues... unless it is possible to have a formatted date in there & the formula can extract the actual day. "JLatham" wrote: Some questions: #1 - Can column A have anything in it other than V, X, Y and/or Z ? i.e., could it have B or R or something else? #2 - exactly what's in column B? Is it text as "Sat" or "Saturday", or is it actually a date that you've formatted to show the day of week for? I'm not saying I can do this, but knowing this information will make the odds of coming up with a solution better. "Sexy Devil" wrote: I have been told by my excel guru that I cant have the following info in one formula. Using Excel 2003 Is this true people? Many thanks for thinking about it. X What I am after is; If cell A is "X or Y or Z" & cell B is Mon, Tues, Wed, Thurs or Fri then multiply cell E by £15.00 but If cell A is "X or Y or Z" & cell B is "Sat" then multiply cell E by £20.00 but If cell A is "X or Y or Z" & cell B is "Sun" then multiply cell E by £25.00 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 00:01 & 08:00 (time) then multiply cell E by £10.00 & cell D by £5 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 08:01 & 18:00 (time) then multiply cell E by £12.00 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 18:01 & 23:59 (time) then multiply cell E by £10.00 & cell D by £5. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Difficult formula required.
Hi Ron, thanks for explaining so far.
cell E and D would be typed in numbers (hours) 1 or 1.25.... the result would be the total cost in the last cell (H) of each row. "Ron Rosenfeld" wrote: On Thu, 26 Nov 2009 08:11:01 -0800, Sexy Devil wrote: I have been told by my excel guru that I cant have the following info in one formula. Using Excel 2003 Is this true people? Many thanks for thinking about it. X What I am after is; If cell A is "X or Y or Z" & cell B is Mon, Tues, Wed, Thurs or Fri then multiply cell E by £15.00 but If cell A is "X or Y or Z" & cell B is "Sat" then multiply cell E by £20.00 but If cell A is "X or Y or Z" & cell B is "Sun" then multiply cell E by £25.00 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 00:01 & 08:00 (time) then multiply cell E by £10.00 & cell D by £5 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 08:01 & 18:00 (time) then multiply cell E by £12.00 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 18:01 & 23:59 (time) then multiply cell E by £10.00 & cell D by £5. It may not be possible because you have not clearly stated your requirements. When you write "multiply cell E by ..." do you want that done within cell E, or are you merely looking for the results of that computation? When you write "multiply cell E by £10.00 & cell D by £5" what, exactly do you mean. Do you want this done within the respective cells, or do you want the sum of these two computations, or do you want the product of these two computations, or do you want something else. If you want to do this with a formula in cell E and cell D, then it is not possible. If you are looking for the results of the computations, then it is possible, depending on what you mean when you are referring to both cells E and D. An example of the first three "IF's" might be: =E2* (AND(OR(A2={"X","Y","Z"}),OR(B2={"Mon","Tues","Wed ","Thurs","Fri"}))*15+ AND(OR(A2={"X","Y","Z"}),B2="Sat")*20+ AND(OR(A2={"X","Y","Z"}),B2="Sun")*25) You can certainly extend this concept to encompass the other conditions, assuming you can answer the above questions. --ron . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Difficult formula required.
No worries, then! The solution I offer is dependent on column A only having
one of those 4 values as legitimate entries, and column B having text of "Mon", "Tue" ... "Sun". I agree with your local Excel guru - darned difficult to build into a single nested formula, but if you break it out into several parts and then create a formula that deals with different parts of it, I believe you'll see that we do come up with something. What all of this does is determine if A = "V" or not, and whether B is either "Sat" or "Sun" and when appropriate, whether the time is <= 8:00 or 18:00 After futzing around with this, my final formula ended up in row 17, so just change the row number as required (probably 2) for the first entry, and fill the formula on down the sheet. Remember that this is one long formula entered into a single cell (other than a cell in column A, B, C, D or E) =IF(A17="V",0,IF(B17="Sat",E17*20,IF(B17="Sun",E17 *25,E17*15))) + IF(A17="V",IF(OR(B17="Sat",B17="Sun"),0,IF(OR(C17< =(8/24),C17(18/24)),0,D17*5)),0) + IF(A17="V",IF(OR(B17="Sat",B17="Sun"),0,IF(OR(C17< =(8/24),C17(18/24)),E17*12,E17*10)),0) format the cell that formula is in as your local currency and hopefully you'll get the results you want. "Sexy Devil" wrote: Hi, thanks for taking the time to look Column A will only have X, Y, Z or V Column B will be just the day Mon, Tues... unless it is possible to have a formatted date in there & the formula can extract the actual day. "JLatham" wrote: Some questions: #1 - Can column A have anything in it other than V, X, Y and/or Z ? i.e., could it have B or R or something else? #2 - exactly what's in column B? Is it text as "Sat" or "Saturday", or is it actually a date that you've formatted to show the day of week for? I'm not saying I can do this, but knowing this information will make the odds of coming up with a solution better. "Sexy Devil" wrote: I have been told by my excel guru that I cant have the following info in one formula. Using Excel 2003 Is this true people? Many thanks for thinking about it. X What I am after is; If cell A is "X or Y or Z" & cell B is Mon, Tues, Wed, Thurs or Fri then multiply cell E by £15.00 but If cell A is "X or Y or Z" & cell B is "Sat" then multiply cell E by £20.00 but If cell A is "X or Y or Z" & cell B is "Sun" then multiply cell E by £25.00 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 00:01 & 08:00 (time) then multiply cell E by £10.00 & cell D by £5 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 08:01 & 18:00 (time) then multiply cell E by £12.00 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 18:01 & 23:59 (time) then multiply cell E by £10.00 & cell D by £5. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Difficult formula required.
To add on..
To your last query if you have a date in B17 use instead of B17="Sat" use one of the below TEXT(B17,"ddd")="Sat" or WEEKDAY(B17)=7 If this post helps click Yes --------------- Jacob Skaria "Sexy Devil" wrote: Hi, thanks for taking the time to look Column A will only have X, Y, Z or V Column B will be just the day Mon, Tues... unless it is possible to have a formatted date in there & the formula can extract the actual day. "JLatham" wrote: Some questions: #1 - Can column A have anything in it other than V, X, Y and/or Z ? i.e., could it have B or R or something else? #2 - exactly what's in column B? Is it text as "Sat" or "Saturday", or is it actually a date that you've formatted to show the day of week for? I'm not saying I can do this, but knowing this information will make the odds of coming up with a solution better. "Sexy Devil" wrote: I have been told by my excel guru that I cant have the following info in one formula. Using Excel 2003 Is this true people? Many thanks for thinking about it. X What I am after is; If cell A is "X or Y or Z" & cell B is Mon, Tues, Wed, Thurs or Fri then multiply cell E by £15.00 but If cell A is "X or Y or Z" & cell B is "Sat" then multiply cell E by £20.00 but If cell A is "X or Y or Z" & cell B is "Sun" then multiply cell E by £25.00 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 00:01 & 08:00 (time) then multiply cell E by £10.00 & cell D by £5 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 08:01 & 18:00 (time) then multiply cell E by £12.00 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 18:01 & 23:59 (time) then multiply cell E by £10.00 & cell D by £5. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Difficult formula required.
"Sexy Devil" wrote:
Column A will only have X, Y, Z or V Column B will be just the day Mon, Tues... unless it is possible to have a formatted date in there & the formula can extract the actual day. I think the solution is much easier to write if B contains a full date. No need to "extract" the day of the week. But if you want B to display only the day of the week, you need to use the Custom format "ddd" or "dddd" without quotes. As for your problem, does the following meet your needs? =IF(OR(A1={"X","Y","Z"}), E1*CHOOSE(WEEKDAY(B1,2),15,15,15,15,15,20,25), IF(WEEKDAY(B1,2)<=5, IF(AND(--"08:00"<C1,C1<=--"18:00"), E1*12, E1*10 + D1*5), 0)) Look up the WEEKDAY help page for an explanation of its usage here. The formula presumes that C1 is actually a numerical time, not text, and not a date as well as time. The zero result at the end is for the condition that you did not specify, namely: A1 is "V", and B1 is Sat or Sun. There is no need to test for A1="V" explicitly. It is implied by the fact that OR(A1={"X","Y","Z"}) is false, and because you said those are the only four values in A1. The "--" (double negation) arithmetic operation effectively converts an hh:mm string to its numerical equivalent. It is equivalent to TIME(18,0,0), for example, which you could use instead, if you prefer. ----- original message ----- "Sexy Devil" wrote in message ... Hi, thanks for taking the time to look Column A will only have X, Y, Z or V Column B will be just the day Mon, Tues... unless it is possible to have a formatted date in there & the formula can extract the actual day. "JLatham" wrote: Some questions: #1 - Can column A have anything in it other than V, X, Y and/or Z ? i.e., could it have B or R or something else? #2 - exactly what's in column B? Is it text as "Sat" or "Saturday", or is it actually a date that you've formatted to show the day of week for? I'm not saying I can do this, but knowing this information will make the odds of coming up with a solution better. "Sexy Devil" wrote: I have been told by my excel guru that I cant have the following info in one formula. Using Excel 2003 Is this true people? Many thanks for thinking about it. X What I am after is; If cell A is "X or Y or Z" & cell B is Mon, Tues, Wed, Thurs or Fri then multiply cell E by £15.00 but If cell A is "X or Y or Z" & cell B is "Sat" then multiply cell E by £20.00 but If cell A is "X or Y or Z" & cell B is "Sun" then multiply cell E by £25.00 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 00:01 & 08:00 (time) then multiply cell E by £10.00 & cell D by £5 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 08:01 & 18:00 (time) then multiply cell E by £12.00 but If cell A is "V" & cell B is "Mon, Tues, Wed, Thurs or Fri" & cell C between 18:01 & 23:59 (time) then multiply cell E by £10.00 & cell D by £5. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Difficult Formula help | Excel Discussion (Misc queries) | |||
Difficult Formula | Excel Worksheet Functions | |||
Difficult Formula | Excel Worksheet Functions | |||
Difficult Formula | Excel Worksheet Functions | |||
Difficult look up formula | Excel Worksheet Functions |