Help with a formula

Could anyone help with a formula? I want to be able to calculate a total based on a column row. If the number is less than 100 it should calculate @ 50 cents, if between 100 and 299 it should calculate @ 75 cents and anything over is calculated at $1. My formula is coming up with unparseable

=IF(AND([Total Attendance]@row<100*"0.5",IF[Total Attendance]@row<100<299*"0.75",IF[Total Attendance]@row>299*"1")))


Any suggestions would be greatly appreciated

Best Answer

  • Brent C. Wilson
    edited 08/03/23 Answer ✓

    =IF([Total Attendance]@row <= 99, 0.5, IF([Total Attendance]@row <= 299, 0.75, 1)) will provide you with the multiplier.

    I think what you are asking is that you then want it to apply that amount to the Total Attendance. Am I right?

    In that case, the formula would be

    =IF([Total Attendance]@row <= 99, 0.5*[Total Attendance]@row, IF([Total Attendance]@row <= 299, 0.75*[Total Attendance]@row, 1*[Total Attendance]@row))

    or

    =IF([Total Attendance]@row <= 99, 0.5*[Data]@row, IF([Total Attendance]@row <= 299, 0.75*[Data]@row, 1*[Data]@row))

    Where "Data" is the row that contains the Data you want to multiply

    Note: I do not have to have the AND Case as the If statement reads left to right. I have already checked for Less than 100 so the next check of less than 299 will not include the 99 or lower as it has already been captured by the first statement.

Answers

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭

    Hi Julieh,

    You can try this formula

    =IF([Total Attendance]@row <= 100, 0.5, IF(AND([Total Attendance]@row > 100, [Total Attendance]@row <= 299), 0.75, IF([Total Attendance]@row > 299, 1, " ")))

    Here, I have converted the currency format to USD for the column ‘Charges’.

    I hope this works for you!

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • Julieh
    Julieh ✭✭
    edited 08/02/23

    Thanks Kaveri but it hasnt calculated properly it returned 0.75. I want it to calculate what the total due is

  • Julieh
    Julieh ✭✭

    Or I could create another column to then calculate that figure x the total attendance

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Here is my suggestion (single column solution):

    =[Total Attendance]@row * IF([Total Attendance]@row<100, 0.5, IF([Total Attendance]@row<299, 0.75, 1))

  • Brent C. Wilson
    edited 08/03/23 Answer ✓

    =IF([Total Attendance]@row <= 99, 0.5, IF([Total Attendance]@row <= 299, 0.75, 1)) will provide you with the multiplier.

    I think what you are asking is that you then want it to apply that amount to the Total Attendance. Am I right?

    In that case, the formula would be

    =IF([Total Attendance]@row <= 99, 0.5*[Total Attendance]@row, IF([Total Attendance]@row <= 299, 0.75*[Total Attendance]@row, 1*[Total Attendance]@row))

    or

    =IF([Total Attendance]@row <= 99, 0.5*[Data]@row, IF([Total Attendance]@row <= 299, 0.75*[Data]@row, 1*[Data]@row))

    Where "Data" is the row that contains the Data you want to multiply

    Note: I do not have to have the AND Case as the If statement reads left to right. I have already checked for Less than 100 so the next check of less than 299 will not include the 99 or lower as it has already been captured by the first statement.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!