1

I've gotten my formula correct for the most part but there is this last part that I can't seem to solve. I am hoping someone here can create a better formula than I.

Here is the formula I have so far:

=IFS($C$5="4-year",MIN(C32,MAX(3000),AND(C38="Y", MIN(C21,SUM(C9-C22)-C39),AND(C37 = "N" , C22>=3000), MAX(0,MIN(3000,C32)),AND(C37 = "N" , C22=3000), MIN(MAX(1000,MIN(C32,C22)),C32), C21>=18000, MAX(0,MIN(18000,C32)), C21<3000, IFS(C22>=3000,MAX(0,MIN(C32,3000)),C22<3000,MAX(0,MIN(C32,3000))), C21<18000, MAX(0,MIN(C32,C21),AND(C5="2-year",MIN(C32,MAX(1000)))))))

My issue is the last part where I don't know how to set up the formula, so that if C5="2-year", there will be a MAX of $1,000 or less. This rule is absolute and should have no other clauses.

This entire formula is input into cell C41

Here is a breakdown of what I need in "Word Form":

If a student is attending a 2-year program, the formula should automatically give cell C41 a maximum of $1,000 or the amount in cell C32, whichever is less.

If the student is attending a 4-year program and cell C37="N" and cell C38="N", the formula should recognize if there is a number greater than one in cell C22. If yes, the formula gives cell C41 a MAX of $3,000 or the amount in cell C32, whichever is less. If there is no amount greater than 1 in cell C22, then cell C41 will have a MAX of $1,000 or the amount in cell C32, whichever is less.

If the student is attending a 4-year program and C37="Y” and C38="N”, the formula should recognize the amount in cell C21 and match that amount with a MAX of $18,000 or the amount in cell C32, whichever is less. If the student has an amount less than 1 in C21, the formula should recognize if there is a number greater than 1 in cell C22. If yes, the formula gives cell C41 a MAX of $3,000 or the amount in cell C32, whichever is less. If not, the formula gives cell C41 a MAX of $1,000 or the amount in C32, whichever is less.

If the student is attending a 4-year program and C37="N” and C38="Y”, the formula should recognize the amount in cell C21 and match the amount with a MAX of cell C32, however, the matched amount in cell C41 cannot be greater than the number manually written in cell C39. If the student has an amount less than 1 in C21, the formula should recognize if there is a number greater than one in C22. If yes, the formula gives cell C41 a MAX of $3,000 or the amount in cell C32, whichever is less. If not, the formula gives cell C41 a max of $1,000 or the amount in cell C32, whichever is less.

If the student is attending a 4-year program and C37="Y” and C38="Y”, the formula should recognize the amount in cell C21 and match the amount with a MAX of cell C32, however, the matched amount in cell C41 cannot be greater than the number manually written in cell C39. If the student has an amount less than 1 in C21, the formula should recognize if there is a number greater than one in C22. If yes, the formula gives cell C41 a MAX of $3,000 or the amount in cell C32, whichever is less. If not, the formula gives cell C41 a max of $1,000 or the amount in cell C32, whichever is less.

I've added a screenshot of what my spread sheet looks like for hopefully better understanding. enter image description here

I would greatly appreciate anyone's input or advice on this.

1
  • Welcome to Web Applications Stack Exchange. You have no doubt put a lot of effort into writing the breakdown, but it would be much better if you could show a table of sample data together with a table that shows your manually entered desired results from that sample data. That would let contributors test their formulas and see if their results match your desired results. Dec 14, 2022 at 16:53

2 Answers 2

1

You should probably rebuild the whole formula, starting from the simplest case, testing that case thoroughly, and only add another case when you are sure that the formula works correctly for the cases you have included so far.

Here is your formula in a bit more readable form:

=ifs( 
  C5 = "4-year", 
    min(C32, 3000), 
  C38 = "y", 
    min(C21, C9 - C22 - C39), 
  and(C37 = "n", C22 >= 3000), 
    max(0, min(3000, C32)), 
  and(C37 = "n", C22 = 3000), 
    min(C32, max(1000, min(C32, C22))), 
  C21 >= 18000, 
    max(0, min(18000, C32)), 
  C21 < 3000, 
    ifs( 
      C22 >= 3000, 
        max(0, min(C32, 3000)), 
      C22 < 3000, 
        max(0, min(C32,3000)) 
    ), 
  C21 < 18000, 
    max(0, min(C32,C21)), 
  C5 = "2-year", 
    min(C32, 1000) 
)

I am quite certain that the formula is not semantically correct. For example, these two cases overlap:

and(C37 = "n", C22 >= 3000)

and(C37 = "n", C22 = 3000)

The first case already tests for equality, so the second case will never be used.

2
  • Thank you for your advice. My formula so far has worked for the most part but it's mostly that last part that kills it. My formula doesn't recognize the C5="2-year",Min(C32,Max(1000)). I don't know why. Do you you have any advice on regards to location or format of this last condition to my formula?
    – Elliot
    Dec 14, 2022 at 17:06
  • You should probably rebuild the whole formula, starting from the simplest case, testing that case thoroughly, and only add another case when you are sure that the formula works correctly for the cases you have included so far. Dec 14, 2022 at 17:08
1

Couldn't agree more with @doubleunary, you should refactor this to be more maintainable. For example, something you could do to clean this up a bit is to remove the unnecessary MAX(1000) instances. The use of MAX(CONSTANT) is always going to return the CONSTANT value. In other words, MIN(A, B) and MIN(A, MAX(B)) are the same, so you can use the former.

That being said, the first 'bug' I noticed was that you are probably missing a closing ) after the first MIN(). See if this fixes it.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.