Calculation simplification needed!!

[Tinribs]

Hey all,

I hope this is the right place for this - if not move it and slap me!

All,I have this formula (inherited from a manager who has left the school).=IF(N$3>$H$3,0,($L8*$E$3+IF($I8>0,-30,0)-O8)*IF(SUM($J8:$K8)>0,0,1))

 

Basically it is working out money owed to the school by students doing extra curricular music lessons. Some students are eligible for free lessons (if they have free school meals or if the lesson is part of their GCSE course etc) all the others have to pay a fixed amount per lesson. They get a discount if they are learning a set pair of instruments.

 

The formula works for everyone who has to pay. If any student is in the FSM or GCSE group then as long as the amount owed is greater than £0.00 then the formula works. If the value owed is less than £0.00 (for example they've paid but have since moved to a FSM or GCSE group) then the amount owed always shows as 0,00 and not - - the sheet should display the credit amount as a red negative value.

Example values for a student in the FSM group are

:N$3 - this is a date

$H$3- also a date

$L8 - is the number of instruments being learned - this is at 1 for all students - I assume this means that if they are learning a set pair that doesn't count as multiple instruments because of the discount.

$E$3 - the current charge for a single lesson

$I8 - this is set to one if the student is entitled to a discount as they are learning a set pair

O8 - value paid which is updated manually by the finance staff $J8 - is set to 1 if student is entitled to free school meals$K8 - is set to 1 is the instrument is being learnt as part of the student's GCSE course

 

 

It seems to me that the problem is that the multiplication operation at the end of the formula is screwing it as it seems to multiply something by 0 if certain conditions are met or by 1 if they are not. Multiplying a negative with a positive number will always give a negative but multiplying with zero will always give zero... I think that's what's breaking it but I struggled to figure out how to fix it so credit amounts show as negative and not as zero until I self-bodged what i think is a very clumsy "solution" as per below:

 

I created two hidden columns.

I moved the original formula in column P and removed the final multiplication statement: - *IF(SUM($J8:$K8)>0,0,1) - to column Q and hid the column.

Added a second formula in column R and hid the column: - =IF((COUNT(J6:K6)>0),0-O6,Q6) - which looks at whether the student is due free lessons or not by checking for numbers in the relevant columns

The amount owed column (P) now has this - =IF(ABS(R7)-O7<=0,R7,ABS(R7)). This makes sure the amount owed is shown in the correct sign (either negative or positive) - if the school is owed the amount is positive, if the student is owed the amount is shown as negative and in red.

 

It works but it seems to me to be very clumsy

I'm sure there is a way of doing this without resorting to using up two extra columns of data. If anyone has any suggestions please feel free to embarrass me! Treat me kindly though as I am very new to this formula lark and am learning on the job..... Literally.

Thanks in advance....

Edited November 2, 2020 by Tinribs

[mavhc]

So the problem is the parent has paid into O8, but the negative amount owed is missing? I'd think that moving O8 to outside of the rest of the calculation should fix it

 

=IF(N$3>$H$3,0,($L8*$E$3+IF($I8>0,-30,0))*IF(SUM($J8:$K8)>0,0,1))-O8

[Tinribs]

That'll do it!

I am suitably embarrassed... Coding, logic, maths, breathing - none of my strong points.

Much appreciated - but I did learn some stuff while fixing it using my tortuous method.....

[mavhc]

Learn something new every day and no day will be wasted