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.

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

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.

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.

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
.

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.

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.

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.