SOLVED

New Contributor

# Calculate a percentage “From / To” range and display the two results in a single cell

Hi

I am trying to work out a heart beat range for exercise.

The maximum heart beat is given in cell “C3”

I wish to train at a minimum to maximum percentage of the value in cell “C3”

EG:
I seek a formula that will show 70% and 80% of cell “C3” and show it with no decimal places and display the output values in a single cell.

I have come up with:    =“”&SUM(C3/100)*(70)&” To “ & SUM(C3/100*(80)

this almost works & outputs:  110.6 To 126.4

However I don’t require the decimal places. I have tried formatting the cell, but this makes no difference.

I believe that the TRUNK or ROUNDDOWN function may work, but I am a novice Excel user and I can’t seem to get it to work. Can anyone help me with the required formula?

4 Replies

# Re: Calculate a from to range and display the two answers in a single cell

Hi James!

I'm not too sure if this is what you're trying to achieve but I hope it helps.

=ROUND(C3/100*70,0)

=ROUND(C3/100*80,0)

You can change ROUND to ROUNDUP or ROUNDDOWN, depending on the answer you're looking for.

best response confirmed by Airbusjames (New Contributor)
Solution

# Re: Calculate a from to range and display the two answers in a single cell

@Airbusjames You need to use the TEXT function if you want to format numbers that become part of a text string. Try this:

``=TEXT(C3*0.7,"0")&" to "& TEXT(C3*0.8,"0")``

As you can see, there is no need to use SUM and /100 and *(70)

If you prefer you may also write the formula like this:

``=TEXT(C3*70%,"0")&" to "& TEXT(C3*80%,"0")``

# Re: Calculate a from to range and display the two answers in a single cell

Thank you so much Riny….. That is exactly what I needed, perfect.

# Re: Calculate a from to range and display the two answers in a single cell

Thank you Li97, unfortunately the system did’t advise me of your reply and I only found it when I was advised of Riny’s reply (below) which does exactly what I need. Your formula looks elegant as well. Thank you for your input.