Forum Discussion
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?
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")
4 Replies
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")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.
- 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.
