SOLVED

# Formulas and Functions

Copper Contributor

# Formulas and Functions

Hello,

I was hoping someone could help me.

I am having trouble attempting to create a formula in my excel spreadsheet.

I would like all the cells in one column to automatically calculate a dollar figure based on numbers entered in an adjacent column.

Example 1: If C6 has a figure between 50.00 & 100.00 D6 should display \$1.50

Example 1: If C6 has a figure between 101.00 & 150.00 D6 should display \$2.00

Example 1: If C6 has a figure between 151.00 & 200.00 D6 should display \$2.50

If there is a formula for this example I would really appreciate any assistance.

Thank you,

Paul

7 Replies

# Re: Formulas and Functions

In D6:

=(ROUNDUP(C6/50,0)+1)/2

Does that do what you want?

# Re: Formulas and Functions

A minor difference of function but the main difference is that I avoid scalar fill-down formulas when an array formula would do the same job!

# Re: Formulas and Functions

Hi Hans,

Unfortunately that didn't work, please see below .

 From To Distance (Klm) Rates Round Up Total Wodonga Wangaratta 69.40 \$        1.50 70.00 \$       105.00 Wodonga Waldara 73.20 \$        1.50 80.00 \$       120.00 Wodonga Glenrowan 86.50 \$        1.50 90.00 \$       135.00 Wodonga Yarrawonga 90.90 \$        1.50 100.00 \$       150.00 Wodonga Benalla 110.30 \$        2.00 120.00 \$       240.00 Wodonga Warrenbayne 129.60 \$        2.00 130.00 \$       260.00 Wodonga Wagga 134.10 \$        2.00 140.00 \$       280.00 Wodonga Gobbagombalin 137.00 \$        2.00 140.00 \$       280.00 Wodonga Coolamon 162.90 \$        2.50 170.00 \$       425.00 Wodonga Mansfield 171.90 \$        2.50 180.00 \$       450.00 Wodonga Mulwala 93.80 \$        1.50 100.00 \$       150.00

I need the rate to increase as per the round up total eg: 50.00 - 100.00 = \$1.50, 101.00 - 150.00 = \$2.00 ect...

best response confirmed by pjukes (Copper Contributor)
Solution

# Re: Formulas and Functions

I pasted your example into a worksheet, then added formulas based on the one in my previous reply.

Sheet 1 is your example, Sheet2 has the formulas. The results appear to be the same.

# Re: Formulas and Functions

This problem seems to be changing by the day?  In addition it is not obvious to me what rate should correspond to 100.5 km.  Anyway, to extend my former solution attempt

``````= LET(
rates, CEILING.MATH(50 + distance, 50) / 100,
rndUp, CEILING.MATH(distance, 10),
HSTACK(rates, rndUp, rates*rndUp)
)``````

# Re: Formulas and Functions

Thank Hans, this formula has done the job. Much appreciated.

# Re: Formulas and Functions

Thanks Peter, My issue has been solved. Appreciate the assistance.
1 best response

Accepted Solutions
best response confirmed by pjukes (Copper Contributor)
Solution

# Re: Formulas and Functions

I pasted your example into a worksheet, then added formulas based on the one in my previous reply.

Sheet 1 is your example, Sheet2 has the formulas. The results appear to be the same.