Jun 27 2017
01:01 PM
- last edited on
Jul 25 2018
09:45 AM
by
TechCommunityAP
Jun 27 2017
01:01 PM
- last edited on
Jul 25 2018
09:45 AM
by
TechCommunityAP
Good Evening
I have made a spreadsheet which is going to be used as our new order form but i'm struggling to finish it off.
I have a section which is called "Hire Duration" and this spreads across B to D.
In each option it has the following options to chose from in a drop down: "6 hours £15" "12 hours £25" "24hours £35".
What i need to do is when a customer selects one of the above the Price/Cost goes into the "Total" section.
The reason it spreads from B to D is because the customer can choose up to 3 different options as per attached.
Jun 27 2017 02:25 PM
Hi James,
That could be something like
=SUMPRODUCT(RIGHT((B20:D20 & "0"),3)*1)/10
assuming any your price is not more than 100 pounds.
Here we add zero to the end of each string to avoid error in calculation - if any of your options is empty RIGHT() from empty string is also empty and we try to add it to any number result will be error.
SUMPRODUCT() sums all resulting figures, e.g.
="150"+"0"+"350"
gives 500 (will be converted to number automatically), finally divide on 10 to receive 50 pounds.
If any of prices is more than 100 pounds the formula will be more complicated
Jun 27 2017 08:40 PM
Another option is to use a SWITCH statement:
=SWITCH(B20,"6 hours $15",15,"12 hours $25",25,"24 hours $35",35)+SWITCH(C20,"6 hours $15",15,"12 hours $25",25,"24 hours $35",35)+SWITCH(D20,"6 hours $15",15,"12 hours $25",25,"24 hours $35",35)
It would get more complicated if you had more than 3 options, but it meets the need!
Jun 28 2017 10:00 AM
Heya,
so just to clarify if i used your formula as it is would that simply get put into the "total cost" field?
From there it should auto calculate ?
Sorry had a long day and my head feels like a slush puppy.