Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Help with a formula

b_tree
b_tree ✭✭
edited 12/09/19 in Archived 2017 Posts

Hello,

This is probably a basic question... I am wondering how to input a formula that would allow me to calculate the value/price of somethingΒ based on a drop-down selection.

For instance, column A would have a drop-down options listingΒ different tree species. For example,Β Redwood, Elm, and Walnut.

Column B would have the board feet of aΒ slab/boardΒ we have in stock.

I want column C to calculate the price of eachΒ board based off of the known value of the species. Β It would know that if the first cell has 'redwood' selected thanΒ the price is $7 per board foot, 'elm' has a price of $8 per board foot, 'walnut' has a price of $17 per board foot, etc. That would be multiplied with the board feet in column B to give me the value of the board.

I'm a spreadsheet rookie. Β Is this possible?

Β 

Comments

  • Mike Wilday
    Mike Wilday Community Champion

    =IF([Column C Name]1 = "Walnut", [Column B Name]1 * 17, IF([Column C Name]1 = "elm", [Column B Name]1 * 8, IF([Column C Name]1 = "redwood", [Column B Name]1 * 7)))

    Should do the trick.

    • Put your own column name in the brackets. If your column name has no white space and doesn't end in a number, then you don't need the brackets.Β 
    • This formula assumes row 1 (hence the 1 after the column name)
    • Format the column you put that formula in with the $ in the tool panel, to get a monetary value.Β 

    Hope that helps.Β 

  • b_tree
    b_tree ✭✭

    Thanks so much, Mike. Β This got me on track.Β Β I just had to swap out [Column C Name] with [Column A Name] because I was ending with a circular reference.

    Exactly what I needed. Β Thank you!

This discussion has been closed.