Calculating markups for multiple value ranges in a single formula in Excel/Sheets

Bobby Vance

Hello! I'm a relative newcomer to spreadsheet formulas. I'm trying to use one formula to create standard markups for items based on fixed cost ranges. I thought I figured out how to use greater and less than in a formula and how to nest them together for multiple values and calculations, but instead of getting a markup calculation, it just returns “False.” I'm not sure if my formula is flawed, if I'm using <>= properly, or what else might be wrong.

I included a chart of the markups below. And here’s the formula I tried based on what I read. It only lists the first two ranges, but once I get that working to calculate properly, I’ll add all 8 ranges into the formula.

=IF(E2<12,E2*4,IF(E2>=12<18,E2*3.7))

E2 is the product cost. So the first range is for products that cost less than $12 with a markup of 4x, the second range is $12-$17.99 with a markup of 3.7x, etc.

I really appreciate any help!

Markup ranges:

Kimberly Loveless

I just want to make sure I am reading this correctly. "E" is the column heading in Smartsheet for the product cost and then you want the the value in column E to be multiplied by the markup amount and have that value in another column where you are entering this formula. Is this correct?

If that is the case you would use a combination of a few formulas

=IF(E@row<12,E@row*4,IF(AND(E@row>=12,E@row<18),E@row*3.7,IF(AND(E@row>=18,E@row<24),E@row*3.4,IF(AND(E@row>=24,E@row<41),E@row*2.9,IF(AND(E@row>=41,E@row<71),E@row*2.4,IF(AND(E@row>=71,E@row<150),E@row*2,IF(AND(E@row>=150,E@row<200),E@row*1.7,IF(AND(E@row>=200,E@row<=300),E@row*1.5

By using the "@row" rather than the row number you can easily make the formula apply to the entire column or use it in other rows

it will look something like this in the sheet when there are values added (I added values to column E and the formula is a column formula in the "Cost with mark up" column)

Kimberly Loveless

I just want to make sure I am reading this correctly. "E" is the column heading in Smartsheet for the product cost and then you want the the value in column E to be multiplied by the markup amount and have that value in another column where you are entering this formula. Is this correct?

If that is the case you would use a combination of a few formulas

=IF(E@row<12,E@row*4,IF(AND(E@row>=12,E@row<18),E@row*3.7,IF(AND(E@row>=18,E@row<24),E@row*3.4,IF(AND(E@row>=24,E@row<41),E@row*2.9,IF(AND(E@row>=41,E@row<71),E@row*2.4,IF(AND(E@row>=71,E@row<150),E@row*2,IF(AND(E@row>=150,E@row<200),E@row*1.7,IF(AND(E@row>=200,E@row<=300),E@row*1.5

By using the "@row" rather than the row number you can easily make the formula apply to the entire column or use it in other rows

it will look something like this in the sheet when there are values added (I added values to column E and the formula is a column formula in the "Cost with mark up" column)

Bobby Vance

Hi @Kimberly Loveless ! Thank you for such a quick and thorough response! Yes, you indeed understood exactly what I am trying to do!

So can you help me further understand the "E@row" syntax? I don't think I understand the difference between "E@row" and selecting the E cell as I build the formula. I would normally click on the reference cell while building the formula and then drag the formula down the column to populate it. But it sounds like maybe there's a better way! I notice in your formula in the screenshot they are all different colors - does that mean you selected the cell when you were building the formula? Or did you actually type literally "E@row". If I copy the text from your response with "E@row" I get an error that says, "Formula parse error" Sorry if that's a dumb question!

Thank you so much!

Paul Newcome

@Bobby Vance I believe you are working in Excel correct? Smartsheet and Excel have different functions and syntax. For instance... "@row" is something that works in Smartsheet but not in Excel. In Excel you have to actually enter the row number.

Similarly... If you leave the "value if false" portion out in a Smartsheet formula, it will automatically output a blank when false, but in Excel it will output "FALSE". To get a "blank if false" output in Excel, you need to actually fill in the "value if false" portion of IF with double quotes that have no space between them (means blank) "".

Paul Newcome

@Kimberly Loveless You can actually use a less bulky formula if you are able to build out a table. If you build out a table similar to the one shown in the original post but instead of putting in the range in each row use the max amount for each variable...

Amount.....Variable

11.99.............4

17.99............3.7

so on and so forth

Then the formula to pull the variable would be...

=INDEX(Variable:Variable, MATCH(MAX(COLLECT(Amount:Amount, Amount:Amount, @cell <= E@row)), Amount:Amount, 0))

Doing it this way means you only need to update the table instead of the formula if anything needs to be added, removed, or changed.

Bobby Vance

Hi @Paul Newcome. Thank you for your help on this! I think the table idea is a good one. Also, to answer your question about programs, I'm actually using Google Sheets, which I believe operates the same as Excel, though you may know differently!

I'm not very advanced in understanding the terminology, so please bear with me! I have a few questions based on the last example you posted:

Amount.....Variable

11.99.............4

17.99............3.7

so on and so forth

Then the formula to pull the variable would be...

=INDEX(Variable:Variable, MATCH(MAX(COLLECT(Amount:Amount, Amount:Amount, @cell <= E@row)), Amount:Amount, 0))

So, do I input the CAPITAL words like "Index", "Match", "Max" as actual words, or do they represent a value I need to select from the table or the source spreadsheet? Also, when you say "Variable:Variable" and "Amount:Amount" I'm not sure which two variables or amounts to select in the table. If you're willing, could you possibly fill in this formula with actual numerical examples from my table? And let me know which are words I need to type and which will be selected in the table or source spreadsheet.

I really appreciate your help!

Paul Newcome

I do know that Google Sheets and Excel also have some differences. You may want to search for a Google Sheets help forum.

As for the formula I provided...

The words in all caps are called "functions". For example, the MAX function will take the largest number from a given range.

In Smartsheet, if you want to reference an entire column, you enter the entire column name twice with a colon. "Amount:Amount".