IF Formula to determine hotel overbudget totals

[Meteorain]

Hi All,

Little help if you don't mind please?

I have a Sheet tracking hotel bookings and require the following:

The budget for a room is £100. If the cost goes over £100, it shows the difference using: =IF(C2>100,C2-100,C2) for example.

The part I'm stuck with is, this formula should only be active IF the number of nights = 1 AND the hotel location is NOT LONDON (as London has a separate budget)

Hotel Nights Cost Difference over £100
London 2 £104.00 £4.00
London 1 £98.00 £98.00
Doncaster 1 £50.00 £50.00
Doncaster 1 £123.00 £23.00
London 1 £145.00 £45.00
Doncaster 2 £101.00 £1.00
London 1 £65.00 £65.00

Any help would be appreciated. (Sheet also attached)

[Glenn Kennedy]

You didn't actually tell us what you expected answers are... so as a guess:

=IF(A2="London", "Separate Budget",IF(AND(B2=1,C2>100),C2-100,""))

[Meteorain]

You didn't actually tell us what you expected answers are... so as a guess:

=IF(A2="London", "Separate Budget",IF(AND(B2=1,C2>100),C2-100,""))

Apologies. The budget for London is £150. Anywhere else is £100.

[Glenn Kennedy]

=IF(A2="London", IF(AND(B2=1,C2>150),C2-150,""),IF(AND(B2=1,C2>100),C2-100,""))

might be what you need...

[AliGW]

Welcome to the forum! Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
  
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

[Meteorain]

Done. Thread Solved. Thank you for your assistance Glenn Kennedy.

[Glenn Kennedy]

You're welcome.

---

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

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.