Discussions

Back to discussions
Expand all | Collapse all

Remove minimum from a group of numbers

  • 1.  Remove minimum from a group of numbers

    Posted 11-15-2019 15:30
    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 except for the lowest/smallest value.
    So if the first 3 fields have the values 5, 1,  7 I need it to give me the response of 12.  

    Is there a way I can calculate this field

    ------------------------------
    Mike Klausing
    ------------------------------


  • 2.  RE: Remove minimum from a group of numbers

    Posted 11-15-2019 15:34
    Try

    Sum ( [field1], ..., [field10]) - Min( [field1], ..., [field10])

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

    Original Message


  • 3.  RE: Remove minimum from a group of numbers

    Posted 11-15-2019 15:36
    how about
    Sum([field 1], [field 2],...[field 10])
    -
    Min(([field 1], [field 2],...[field 10])

    but what if there is only one or two values??


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------

    Original Message


  • 4.  RE: Remove minimum from a group of numbers

    Posted 11-18-2019 08:20

    That formula should work except in the case where fields 8, 9, and 10 etc are blank or zero. I need to have something that subtracts the Min of those values 1-10 that are also greater than 0

     

    Thank you,
    Mike Klausing

     




    Original Message


  • 5.  RE: Remove minimum from a group of numbers

    Posted 11-15-2019 15:40
    Edited by Adam Keever 11-19-2019 09:52
    ---removed post---
    ------------------------------
    Adam Keever
    ------------------------------

    Original Message


  • 6.  RE: Remove minimum from a group of numbers

    Posted 11-19-2019 09:57
    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
    ------------------------------

    Original Message


  • 7.  RE: Remove minimum from a group of numbers

    Posted 11-20-2019 13:10
    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
    ------------------------------

    Original Message


  • 8.  RE: Remove minimum from a group of numbers

    Posted 11-21-2019 08:43
    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
    ------------------------------

    Original Message


  • 9.  RE: Remove minimum from a group of numbers

    Posted 11-21-2019 14:06

    Adam,

     

    Thank you! That is working great for me now.

     

    Thank you,
    Mike Klausing

     




    Original Message