How to narrow this formula down?

[albertc30]

Hi everyone.

It has been quite sometime since I was last here, but with becoming a father in recent years and starting a family, time is realy scarce.

Anyways, I hope that this post finds all members, old and new, in good health and in good spirits.

Now, down to the question. I have this working so this is not of much importance. However, as I have always been very impressed with some brilliant minds in here, how would one shorthen this formula?

=IF(OR(E25="",E25="No"),,IF(AND(D25="Saturday",E25="Yes"),50,IF(AND(D25="Saturday",E25="Yes/On-Call"),95,IF(AND(D25="Sunday",E25="Yes/On-Call"),95,IF(AND(D25="Saturday",E25="On-Call"),45,IF(AND(D25="Sunday",E25="On-Call"),45,IF(AND(D25="Monday",E25="Yes/On-Call"),179.64,IF(AND(D25="Tuesday",E25="Yes/On-Call"),179.64,IF(AND(D25="Wednesday",E25="Yes/On-Call"),179.64,IF(AND(D25="Thursday",E25="Yes/On-Call"),179.64,IF(AND(D25="Friday",E25="Yes/On-Call"),179.64,134.64)))))))))))

Like I said, this is working but I am positive there's a better and shorter version to achieve the same result and I have busted my mind but the above is all I could come up with.

Once again, this is not of any importance and the formula is working as intended. For all of you out there, a much big thanks for all the help you have offered me in the past and for all you current help and generosity in giving your precious time into trying and helping other, something I hope to do too in the not so distante future.

Much appreciated for everything.

Regards,
Albert

 

[Proulxs]

The way I would have done it is put down a table of the value like Yes, Yes/On-call, On-call, No. and the lines being the day of the week.
In the cross between Saturday/Yes it would be 50, Saturday/No 0, etc
Advantage being it's much easier to edit if you want to change pricing and it's more visual too.

Then you can use Match to find the line number and column number the info provided in D25/E25 is refering to and put these into an Index.

So let's say the table is in A1 to E8 and Day you input is still in D25, the service provided is in E25 still;
=index(B2:D8,match(D25,A2:A8,0),match(E25,B1:E1,0))

You may still want to add something to catch an empty field, especially if you want to charge 134.64 if the day is filled but no service info was given...
But that's my idea of how I would handle it. That's why excel is sometimes to fun, you can do it so many ways

Simon

 

[albertc30]

The way I would have done it is put down a table of the value like Yes, Yes/On-call, On-call, No. and the lines being the day of the week.
In the cross between Saturday/Yes it would be 50, Saturday/No 0, etc
Advantage being it's much easier to edit if you want to change pricing and it's more visual too.

Then you can use Match to find the line number and column number the info provided in D25/E25 is refering to and put these into an Index.

So let's say the table is in A1 to E8 and Day you input is still in D25, the service provided is in E25 still;
=index(B2:D8,match(D25,A2:A8,0),match(E25,B1:E1,0))

You may still want to add something to catch an empty field, especially if you want to charge 134.64 if the day is filled but no service info was given...
But that's my idea of how I would handle it. That's why excel is sometimes to fun, you can do it so many ways

Simon

Oh Simon! LOL...

Now that you mention it, it makes so much more sense!

The various ways to have fun on excel!!!

Thank you for your imput Simon. It is much appreciated.

Regards,
Albert

 

[Dave Patton]

When is 134.64 applicable?
Two alternatives that you can try or you can look at Index / Match.
The two criteria are concatenated for example =D4&E4 in order to simplify the lookup.

T202211a.xlsm
	A	B	C	D	E	F	G
1
2		134.64	134.64
3
4				Saturday	Yes	SaturdayYes	50.00
5				Saturday	On-call	SaturdayOn-call	45.00
6				Sunday	Yes/On-call	SundayYes/On-call	95.00
7				Sunday	On-call	SundayOn-call	45.00
8				Monday	Yes/On-call	MondayYes/On-call	179.64
9				Tuesday	Yes/On-call	TuesdayYes/On-call	179.64
10				Wednesday	Yes/On-call	WednesdayYes/On-call	179.64
11				Thursday	Yes/On-call	ThursdayYes/On-call	179.64
12				Friday	Yes/On-call	FridayYes/On-call	179.64
13					Yes	Yes	134.64
14
9g

Cell Formulas
Range	Formula
B2	B2	=IF(OR(E25={"","No"}),"",IF(AND(D25="Saturday",E25="Yes"),50,IF(AND(OR(D25={"Saturday","Sunday"}),E25="Yes/On-Call"),95,IF(AND(OR(D25={"Saturday","Sunday"}),E25="On-Call"),45,IF(AND(OR(D25={"Monday","Tuesday","Wednesday","Thursday","Friday"}),E25="Yes/On-Call"),179.64,134.64)))))
C2	C2	=VLOOKUP(D25&E25,F4:G13,2,0)

 

[albertc30]

When is 134.64 applicable?
Two alternatives that you can try or you can look at Index / Match.
The two criteria are concatenated for example =D4&E4 in order to simplify the lookup.

T202211a.xlsm
	A	B	C	D	E	F	G
1
2		134.64	134.64
3
4				Saturday	Yes	SaturdayYes	50.00
5				Saturday	On-call	SaturdayOn-call	45.00
6				Sunday	Yes/On-call	SundayYes/On-call	95.00
7				Sunday	On-call	SundayOn-call	45.00
8				Monday	Yes/On-call	MondayYes/On-call	179.64
9				Tuesday	Yes/On-call	TuesdayYes/On-call	179.64
10				Wednesday	Yes/On-call	WednesdayYes/On-call	179.64
11				Thursday	Yes/On-call	ThursdayYes/On-call	179.64
12				Friday	Yes/On-call	FridayYes/On-call	179.64
13					Yes	Yes	134.64
14
9g

Cell Formulas
Range	Formula
B2	B2	=IF(OR(E25={"","No"}),"",IF(AND(D25="Saturday",E25="Yes"),50,IF(AND(OR(D25={"Saturday","Sunday"}),E25="Yes/On-Call"),95,IF(AND(OR(D25={"Saturday","Sunday"}),E25="On-Call"),45,IF(AND(OR(D25={"Monday","Tuesday","Wednesday","Thursday","Friday"}),E25="Yes/On-Call"),179.64,134.64)))))
C2	C2	=VLOOKUP(D25&E25,F4:G13,2,0)

Hi Dave.

Sorry for the delay.

That sounds a great aproach. I can see that ebing much better. But I must admit I never played around with Index much let alone Match. I will give it a go.

Much appreciated for your reply and thanks for helping.

Have a good weekend everyone.

Regards,
Albert

 

[Dave Patton]

If your concern is using the table, the table is not required if you name the array of lookup information.

Name the information with Formulas Name Manager.

You can just enter the information or move to cell C2 in my example, highlight F4:G15 and press key F9, and select the information.

In Name Manager
Name aL
Value ={"",0;"No",0;"SaturdayYes",50;"SaturdayOn-call",45;"SundayYes/On-call",95;"SundayOn-call",45;"MondayYes/On-call",179.64;"TuesdayYes/On-call",179.64;"WednesdayYes/On-call",179.64;"ThursdayYes/On-call",179.64;"FridayYes/On-call",179.64;"Yes",134.64}

T202211a.xlsm
	A	B	C	D	E	F	G
1
2		179.64	179.64	179.64
3
4							0
5					No	No	0
6				Saturday	Yes	SaturdayYes	50.00
7				Saturday	On-call	SaturdayOn-call	45.00
8				Sunday	Yes/On-call	SundayYes/On-call	95.00
9				Sunday	On-call	SundayOn-call	45.00
10				Monday	Yes/On-call	MondayYes/On-call	179.64
11				Tuesday	Yes/On-call	TuesdayYes/On-call	179.64
12				Wednesday	Yes/On-call	WednesdayYes/On-call	179.64
13				Thursday	Yes/On-call	ThursdayYes/On-call	179.64
14				Friday	Yes/On-call	FridayYes/On-call	179.64
15					Yes	Yes	134.64
16
9g

Cell Formulas
Range	Formula
B2	B2	=IF(OR(E25={"","No"}),"",IF(AND(D25="Saturday",E25="Yes"),50,IF(AND(OR(D25={"Saturday","Sunday"}),E25="Yes/On-Call"),95,IF(AND(OR(D25={"Saturday","Sunday"}),E25="On-Call"),45,IF(AND(OR(D25={"Monday","Tuesday","Wednesday","Thursday","Friday"}),E25="Yes/On-Call"),179.64,134.64)))))
C2	C2	=VLOOKUP(D25&E25,F4:G15,2,0)
D2	D2	=VLOOKUP(D25&E25,aL,2,0)

 

[Dave Patton]

T202211a.xlsm
	D	E	F
27	Sunday	On-call	45
9g

Cell Formulas
Range	Formula
F27	F27	=VLOOKUP(D27&E27,aL,2,0)