SOLVED

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

Copper Contributor

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

@Airbusjames 

 

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 (Copper Contributor)
Solution

@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")

 

@Riny_van_Eekelen 

 

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

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.
1 best response

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

@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")

 

View solution in original post