Forum Discussion

jaredemmer's avatar
jaredemmer
Copper Contributor
Feb 10, 2020

Needing Help with a formula

Hello,

 

I am trying to make a formula where a cell is based on several different factors.  I want the cell to calculate a roof priced based off the type of roof, how many squares, and the selected interest rate.

 

I did one type with a bunch of If statements.  I have tried to compile it all with If statements but it exceeds the 64 statements allowed.  

 

I have attached two screen shots. So when the user selects a financing option, and a roof type, it should correspond with the Input sheet cost.

Formulas and Functions

4 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 10, 2020

    jaredemmer 

    Always difficult to help on the basis of a few screen shots. Though, one observation I made is that the  prices for each type of roof are based on a fixed price per Sqft (at least for the first 17 sizes visible in the screenshot with the Input sheet). Then there is no need to list out all the prices for every possible size. Just use Size X Sqft Price to calculate the total cost. This might eliminate much of the IF statements that you say to be using. Not sure, though, that this is the case. Therefore, it would be good if you could upload a sample of your workbook (without any confidential information) indicating what values/numbers you expect to see where and based on what logic.

     

     

    • jaredemmer's avatar
      jaredemmer
      Copper Contributor
      Feb 10, 2020

      Riny_van_Eekelen

       

      Thank you for the response.  The reason why I listed them all out is that they change with the selected interest rate so its not a fixed price.  If you were to select a different term for the financing amount, the cost of the roof will change.  

       

      So for example:

      The 10 year 2.99% Comp Roof = $468

      The 25 Year 3.49% Comp Roof = $489

       

      So every different financing option,  the price change.  

       

      I did the comp roof with a bunch of if statements.  

       

      I can't add the other two roof selections

       

      I want to have the drop down box of those three roof types. If I select wood shake, I want to select how many squares and have it equal that value it corresponds with.

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Feb 10, 2020

        jaredemmer Would be better if you could demonstrate in a workbook what you have achieved so far. It seems that you are not trying to accomplish something very complicated. Excel offers a variety of lookup and matching functions and drop-downs are easy to create. Don't really understand why you would need a lot of IF functions at all. But.... I would have to see your schedule to be sure.

Resources