Forum Discussion

MikeKlausing's avatar
MikeKlausing
Qrew Assistant Captain
6 years ago

Remove minimum from a group of numbers

I have 10 formula numeric Fields being calculated into number [total kicker cost].  Kicker Quantity 1st, Kicker Quantity 2nd...Kicker Quantity 10th I need to add up all of these fields together ex...
Formulas and functions
AdamKeever1's avatar
AdamKeever1
Qrew Commander
6 years ago
Try this Mike:

Sum([Field1],[Field2],[Field3],[Field4])-

Min(If([Field1]>0,[Field1],null),
If([Field2]>0,[Field2],null),
If([Field3]>0,[Field3],null),
If([Field4]>0,[Field4],null))​


Here is what it returns; works with blanks too:


------------------------------
Adam Keever
------------------------------
MikeKlausing's avatar
MikeKlausing
Qrew Assistant Captain
6 years ago
Here is the formula I used: 

([Total Kicker Count (1-10)]*[Kicker - Total]) -Min(If([Kicker cost 1st]>0,[Kicker cost 1st],0),
If([Kicker Cost 2nd]>0,[Kicker Cost 2nd],0),
If([Kicker Cost 3rd]>0,[Kicker Cost 3rd],0),
If([Kicker Cost 4th]>0,[Kicker Cost 4th],0),
If([Kicker Cost 5th]>0,[Kicker Cost 5th],0),
If([Kicker Cost 6th]>0,[Kicker Cost 6th],0),
If([Kicker Cost 7th]>0,[Kicker Cost 7th],0),
If([Kicker Cost 8th]>0,[Kicker Cost 8th],0),
If([Kicker Cost 9th]>0,[Kicker Cost 9th],0),
If([Kicker Cost 10th]>0,[Kicker Cost 10th],0))

This still gives me the full cost and isnt subtracting the minimum cost

------------------------------
Mike Klausing
------------------------------
  • AdamKeever1's avatar
    AdamKeever1
    Qrew Commander
    6 years ago
    Use this Mike:

    Sum([Kicker cost 1st],[Kicker Cost 2nd],[Kicker Cost 3rd],[Kicker Cost 4th],[Kicker Cost 5th],[Kicker Cost 6th],[Kicker Cost 7th],[Kicker Cost 8th],[Kicker Cost 9th],[Kicker Cost 10th])-

Min(If([Kicker cost 1st]>0,[Kicker cost 1st],null),
If([Kicker Cost 2nd]>0,[Kicker Cost 2nd],null),
If([Kicker Cost 3rd]>0,[Kicker Cost 3rd],null),
If([Kicker Cost 4th]>0,[Kicker Cost 4th],null),
If([Kicker Cost 5th]>0,[Kicker Cost 5th],null),
If([Kicker Cost 6th]>0,[Kicker Cost 6th],null),
If([Kicker Cost 7th]>0,[Kicker Cost 7th],null),
If([Kicker Cost 8th]>0,[Kicker Cost 8th],null),
If([Kicker Cost 9th]>0,[Kicker Cost 9th],null),
If([Kicker Cost 10th]>0,[Kicker Cost 10th],null))​


    This works on all of the scenarios I have tested so far...


    ------------------------------
    Adam Keever
    ------------------------------
    • MikeKlausing's avatar
      MikeKlausing
      Qrew Assistant Captain
      6 years ago

      Adam,

       

      Thank you! That is working great for me now.

       

      Thank you,
      Mike Klausing

       



      ------Original Message------

      Use this Mike:

      Sum([Kicker cost 1st],[Kicker Cost 2nd],[Kicker Cost 3rd],[Kicker Cost 4th],[Kicker Cost 5th],[Kicker Cost 6th],[Kicker Cost 7th],[Kicker Cost 8th],[Kicker Cost 9th],[Kicker Cost 10th])-

Min(If([Kicker cost 1st]>0,[Kicker cost 1st],null),
If([Kicker Cost 2nd]>0,[Kicker Cost 2nd],null),
If([Kicker Cost 3rd]>0,[Kicker Cost 3rd],null),
If([Kicker Cost 4th]>0,[Kicker Cost 4th],null),
If([Kicker Cost 5th]>0,[Kicker Cost 5th],null),
If([Kicker Cost 6th]>0,[Kicker Cost 6th],null),
If([Kicker Cost 7th]>0,[Kicker Cost 7th],null),
If([Kicker Cost 8th]>0,[Kicker Cost 8th],null),
If([Kicker Cost 9th]>0,[Kicker Cost 9th],null),
If([Kicker Cost 10th]>0,[Kicker Cost 10th],null))​


      This works on all of the scenarios I have tested so far...


      ------------------------------
      Adam Keever
      ------------------------------