Formula to calculate cancellation charges due

**duckersj** · 02-24-2017, 06:47 AM

Hi there

I need to use a complicated formula, but can't work out how it should read

I have columns

I - Nomination Date
K - Expected Start date
L - Cancellation Date
N - Date of Enrolment
O- Date of Attendance
U - Attendance Complete (Y or N)
V - Failed to Complete Component (Y or N)

People who
cancel their place after being nominated but before enrolment should pay nothing (Date in Cancellation and Nomination fields, but Enrolment field will be empty).
cancel their place after enrolment but before start date will be subject to £1000 (Date in Cancellation, Nomination and Enrolment fields)
do not enrol or cancel their place or attend will be subject to £1000 (so fields L, N AND O will be empty)
fail to complete component will be due £200 ("Y" will be in column "V")
fail to complete Attendance will be due £200 ("N" will be in column "U")

I need to show the cancellation charge due (£0, £200 or £1000) in another column

Can anyone help?

Thanks

**Special-K** · 02-24-2017, 07:56 AM

Untested.

Try this (run this against some varying test data to check results)

=IF(AND(L1 < > 0,I1 < > 0,N1 < > 0,L1 > N1,L1 < K1),1000,IF(OR(N1=0,L1 < > 0,O1=0),1000,IF(OR(V1="Y",U1="N"),200,0)))

This should be ok but results may depend on the order in which you have described the conditions (I cant see any obvious conflicts with the conditions you have described).

**duckersj** · 02-24-2017, 08:26 AM

Thank you Special K - I appreciate your help very much - I've been trying to work this formula out for a couple of days now -

You'll be glad to know that it's working fine

Can I ask a further question please (I'm being cheeky I know

- but the spreadsheet includes candidates who have not sent a nomination back (so all columns will be empty). How do I amend the formula to show that they are should be charged "0".

Hope the rest of the day is not too busy - it is nearly the weekend!

Cheers
duckersj

**Special-K** · 02-24-2017, 08:34 AM

If a candidate does not meet the £1000 or £200 cancellation charge then the result should be £0

**jeffreybrown** · 02-24-2017, 08:40 AM

Hello duckersj & Welcome to the Forum,

Administrative Note:

We would love to continue to help you with your query, but first, before we can proceed…
Please see Forum Rule #1 about proper thread titles and adjust accordingly...

**Special-K** · 02-24-2017, 08:42 AM

Oops! I should probably have read that rule as well!
Sorry Mod.

**jeffreybrown** · 02-24-2017, 08:44 AM

No Worries. Just earning some side money before I head off to work!

**kev_** · 02-24-2017, 08:51 AM

I avoid complicated formulas - they scare me!

Instead

Helper Columns X:AB, calculated charge in W
Formula
X2

Formula:

Please Login or Register  to view this content.

Y2

Formula:

Please Login or Register  to view this content.

Z2

Formula:

Please Login or Register  to view this content.

AA2

Formula:

Please Login or Register  to view this content.

AB2

Formula:

Please Login or Register  to view this content.

W2

Formula:

Please Login or Register  to view this content.

**duckersj** · 02-24-2017, 09:32 AM

Hello

Thank you for your help it is appreciated very much !

I should have really tried to break it down - it is much easier -

can you advise though - we should only be charging £200 fee it they fail to complete or do not complete attendance -(at the moment if they do not do both then the calculation is showing that they have to pay £400) How do I work the calculation so that the amount only shows the £200.
Also as some people have no nomination date how do I set the fees to "£0" as candidates have not taken up the offer - so should not be charged.

duckersj

**duckersj** · 02-24-2017, 09:39 AM

Hi Jeff

I cannot edit the post - as soon as I click edit I just get a spinning wheel... I cannot see the "go advanced" button anywhere??

duckersj

**kev_** · 02-24-2017, 10:05 AM

we should only be charging £200 fee it they fail to complete or do not complete attendance -(at the moment if they do not do both then the calculation is showing that they have to pay £400) How do I work the calculation so that the amount only shows the £200.

Put this formula in either of the 2 columns and delete the other column

Formula:

Please Login or Register  to view this content.

**Special-K** · 02-24-2017, 10:13 AM

...can be simplified to

=OR(V2="Y",U2="N")*200

**kev_** · 02-24-2017, 10:51 AM

Well spotted Special-K

as some people have no nomination date how do I set the fees to "£0" as candidates have not taken up the offer - so should not be charged.

Silly question perhaps - why are they on the list if they have not been nominated?

WARNING: do not copy and paste these formulas - apply the logic

NOTE: below assumes that you have done what was suggested in post#11

LOGIC = To make a charge, then column I must contain a value. We need to add an extra =AND condition to reflect this to all 4 remaining columns

=AND
(3 columns contain =AND formulas)
we want all conditions to be true to trigger a charge, so you need to add an extra condition that I2 is not blank inside the original =AND
eg

Formula:

Please Login or Register  to view this content.

becomes
=AND(N2="",L2="",O2="",I2<>"")*1000

=OR
(only 1 column contains =OR formula)
we need to add the extra condition because we want I2 not to be empty AND regardless of what is inside the OR function
eg

Formula:

Please Login or Register  to view this content.

becomes
=AND(I2<>"",OR(V2="Y",U2="N"))*200

**duckersj** · 02-24-2017, 10:52 AM

Thank you

**duckersj** · 02-24-2017, 10:54 AM

Yes - It is a bit of silly wording...

In essence, they have been identified as possible attendees - but a completed application form confirms the "nomination".

duckersj

**FDibbins** · 02-25-2017, 02:01 PM

Guys, for future reference, please do not respond to a thread that is waiting for a moderation to be complied with. I see the OP has changed the title, and reported in post 10 they could not change it, this should have been brought to a mod/admin's attention so it could be rectified

**duckersj** · 02-26-2017, 09:11 AM

I reported that I could not change it, but managed to do do later on, I think by trial and error....had no response from mod/admin.

**Doc.AElstein** · 02-27-2017, 10:30 AM

Originally Posted by duckersj

I reported that I could not change it, but managed to do do later on, ....

It might be that the Forum software does not let you initially do things like Edit.
This is done, I think, to prevent a Spammer registering and messing about changing his first post to hide, or add things later...
The exact details of how this works are not made public, I think, so as not to give any insight into a Spammer.
But once you appear “genuine”, you should find things like this will be available and “work” for you.

Formula to calculate cancellation charges due

LinkBack

Thread Tools

Rate This Thread

Display

Formula to calculate cancellation charges due

Re: Complicated formula needed

Re: Complicated formula needed

Re: Complicated formula needed

Re: Complicated formula needed

Re: Complicated formula needed

Re: Complicated formula needed

Re: Complicated formula needed

Re: Complicated formula needed

Re: Complicated formula needed

Re: Complicated formula needed

Re: Complicated formula needed

Re: Complicated formula needed

Re: Complicated formula needed

Re: Complicated formula needed

Re: Formula to calculate cancellation charges due

Re: Formula to calculate cancellation charges due

Re: Formula to calculate cancellation charges due

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula Help... Calculating charges based on stepped rates.

Formula for quarterly charges

Formula to Sum Charges Based on Dates for each Patient

InputBox Cancellation

Formula to work out additional charges

VBA to calculate charges

[SOLVED] How do I calculate charges based on elasped time(H:MM) & rate($)?

Bookmarks

Bookmarks

Posting Permissions