IF formula with a calculation

I am new to smartsheet and I want to combine an IF statement with a calculation. I have instruments that I bill differently. So I want to say if the instrument is the Confocal Scope then multiply the number of hours entered by 15. If it is a laser scope, it multiply the number of hours by 30. So I got a part of a formula =IF([Instrument or Service]1 = "MSC Andor Confocal", THEN =Hours@row * 15) but I am not sure how to add that function for multiplying the hours and apply it to the whole new column called Billable Time. What I have is wrong.  I also dont know if I need separate billable time columns for the instruments where I charge 15/hr vs 30/hr. I managed to do a workflow for a fixed billing price because what I told it to do is : if a person was using a certain instrument to put in 100 in the billable time column. But I cant seem to do workflow for these other formulas. Thank you!

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Freia66

    =IF([Instrument or Service]@row="MSC Andor Confocal",Hours@row * 15, IF([Instrument or Service]@row="Laser Scope",Hours@row * 30))

    Check the wording I inserted for your Laser Scope and edit the formula so that the wording is an exact match to how your Laser Scope is worded. Also, because you are doing multiplication on the Hours column, you must have a numeric value in the cell or you will get a error. If you see this, let me know and we can mitigate it.

    If you have more instruments to add, use the same pattern of adding an IF statement after the preceding comma. Note that your parentheses will all be add the very end.

    You should be able to place the above formula (after verifying the laser scope wording) into your Billable Time column and convert this to a column formula (right click on formula and scroll to bottom of menu to Convert to Column Formula). This will fill the entire column for you.

    Use @mention if you have any questions, or if I misunderstood what you needed.

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Freia66

    =IF([Instrument or Service]@row="MSC Andor Confocal",Hours@row * 15, IF([Instrument or Service]@row="Laser Scope",Hours@row * 30))

    Check the wording I inserted for your Laser Scope and edit the formula so that the wording is an exact match to how your Laser Scope is worded. Also, because you are doing multiplication on the Hours column, you must have a numeric value in the cell or you will get a error. If you see this, let me know and we can mitigate it.

    If you have more instruments to add, use the same pattern of adding an IF statement after the preceding comma. Note that your parentheses will all be add the very end.

    You should be able to place the above formula (after verifying the laser scope wording) into your Billable Time column and convert this to a column formula (right click on formula and scroll to bottom of menu to Convert to Column Formula). This will fill the entire column for you.

    Use @mention if you have any questions, or if I misunderstood what you needed.

    Kelly

  • Freia66
    Freia66 ✭✭

    Kelly thank you very much for the syntax. I will try it out and see how it works!!!

  • Freia66
    Freia66 ✭✭

    And it works great! Thank yoU!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!