Calculator for how much people need to pay when we go on holiday

[Silkster]

Hi guys,

I have a spreadsheet I'm trying to make which determines how much people need to pay when we go on holiday. There are multiple guests, and they are not staying for the same amount of time during the holiday.
Please see the attachment, as Im undoubtably not going to explain it very well.

I'll use 1 cell as an example of what I want to achieve;
I need E18 to become one of seven values (B7, B8, B9, B10, B11, B12) depending on what the value of D26 is.

so, if D26 is 3, then E18 value should become 17.22, but only if I put a value in D18.
If D18 has no value, E18 should be 0 or have no value.

Please see attached file.

Thanks in advance everyone!

T

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I have done it for you this time.)

[AliGW]

In E18:

=IF(D18="","",INDEX($B$7:$B$12,MATCH(D$26,$A$7:$A$12,0)))

However, you need to change the lookup range to this:

Excel 2016 (Windows) 32 bit

	A	B
7	7	£7.38
8	6	£8.61
9	5	£10.33
10	4	£12.92
11	3	£17.22
12	2	£25.83

Sheet: Sheet1

[Silkster]

Wow, that's pretty neat. Ive not used MATCH before, now I'm going to look up how to use that more often.

subsequent quesion; using that formula has made B18 (where the formula is =E18+G18+I18+K18+M18+O18+Q18+S18+U18+W18+Y18+AA18+AC18+AE18+AG18+AI18) give an error #VALUE!, same with B26.

Since I'm not super familiar with the new formula, I'm not sure why this is happening.

Thoughts?

Cheers!

T

[AliGW]

The formula in that cell in your sample file is this:

=SUM(E18:AK18)

which, of course, is wrong anyway.

Try this instead:

=SUMIF($E$17:$AK$17,"<>",$E18:$AK18)

[Silkster]

I think I solved it, I added a 0
original
=IF(D18="","",INDEX($B$7:$B$12,MATCH(D$26,$A$7:$A$12,0)))
amended
=IF(D18="","0",INDEX($B$7:$B$12,MATCH(D$26,$A$7:$A$12,0)))

[AliGW]

No need to do that - not efficient or pretty.

See post #5.

[Silkster]

Thanks AliGW, mind blown.

much appreciated.

T

[AliGW]

Workbook attached above.

You are obviously at the start of your Excel journey - stick with us here and you'll learn fast.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.