format numbers after calculation

H

HankJ

Board Regular
Joined
Mar 5, 2016
Messages
89
Part of my work involves dose response determinations. We use a half log dilution to make up 9 concentrations.

The top concentration can be anything from 500 to 1.

Using these two as examples the numbers look like this, after manually formatting each cell.

[TABLE="width: 769"]
<colgroup><col><col span="8"></colgroup><tbody>[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="align: center"]500.[/TD]
[TD="align: center"]158[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]15.8[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1.58[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]0.158[/TD]
[TD="align: center"]0.05[/TD]
[/TR]
[TR]
[TD="align: center"]1.[/TD]
[TD="align: center"]0.32[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"]0.032[/TD]
[TD="align: center"]0.01[/TD]
[TD="align: center"]0.0032[/TD]
[TD="align: center"]0.001[/TD]
[TD="align: center"]0.00032[/TD]
[TD="align: center"]0.0001[/TD]
[/TR]
</tbody>[/TABLE]

What I would like is to have a way of making sure there is a meaningful number that I can copy and paste into another sheet.

I'm happy with either formulae in the excel sheet or having it driven by some VBA code.

Many thanks

HankJ
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
HankJ said:
Part of my work involves dose response determinations. We use a half log dilution to make up 9 concentrations.

The top concentration can be anything from 500 to 1.

Using these two as examples the numbers look like this, after manually formatting each cell.

[TABLE="width: 769"]
<tbody>[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"]7
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]9
[/TD]
[/TR]
[TR]
[TD="align: center"]500.
[/TD]
[TD="align: center"]158
[/TD]
[TD="align: center"]50
[/TD]
[TD="align: center"]15.8
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]1.58
[/TD]
[TD="align: center"]0.5
[/TD]
[TD="align: center"]0.158
[/TD]
[TD="align: center"]0.05
[/TD]
[/TR]
[TR]
[TD="align: center"]1.
[/TD]
[TD="align: center"]0.32
[/TD]
[TD="align: center"]0.1
[/TD]
[TD="align: center"]0.032
[/TD]
[TD="align: center"]0.01
[/TD]
[TD="align: center"]0.0032
[/TD]
[TD="align: center"]0.001
[/TD]
[TD="align: center"]0.00032
[/TD]
[TD="align: center"]0.0001
[/TD]
[/TR]
</tbody>[/TABLE]

What I would like is to have a way of making sure there is a meaningful number that I can copy and paste into another sheet.
Click to expand...
If the 500, 158, 50, etc. values are in Row 2 and you want the values you show in Row 3, use this formula (assuming the first concentration is in Column A)...

=A2/500
 
Upvote 0
Hi Rick R

Thank you for the quick reply. I'm sorry if I was not clearer in my problem.
Line 2 is the First dose response and Line 3 is a second dose response.

If I paste what the numbers look like without manual formatting it would look something like this:

[TABLE="width: 769"]
<colgroup><col><col span="8"></colgroup><tbody>[TR]
[TD]1
[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD]158.113516[/TD]
[TD]49.999768[/TD]
[TD]15.811278[/TD]
[TD]4.999954[/TD]
[TD]1.581120[/TD]
[TD]0.499993[/TD]
[TD]0.158111[/TD]
[TD]0.049999[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0.316227[/TD]
[TD]0.100000[/TD]
[TD]0.031623[/TD]
[TD]0.010000[/TD]
[TD]0.003162[/TD]
[TD]0.001000[/TD]
[TD]0.000316[/TD]
[TD]0.000100[/TD]
[/TR]
</tbody>[/TABLE]

When I come to copy these values to another sheet the 0.100000 in Row 3 has too many "0"'s and the 4.999954 in Row 2 should be 5.

Again thank you

Hankj
 
Upvote 0
Formatting won't change the values so will make no difference when you copy them.

What you probably want is the ROUND function.
 
Upvote 0
Hi Norrie

Yes, I'm working on ROUNDing but finding it tricky to control the number of decimal places.... and some of the circular arguments are doing my head in.

Thanks for the suggestion.

Hankj
 
Upvote 0
What circular arguments?

All you should need is ROUND(<yourformula>, 2).
 
Upvote 0
though 0.001 or 0.0003 would require a different value. The trick, as I see it, is to fit the rounding number to the actual number of decimal points, automatically or by code.

Cheers

Hankj
 
Upvote 0
HankJ said:
though 0.001 or 0.0003 would require a different value. The trick, as I see it, is to fit the rounding number to the actual number of decimal points, automatically or by code.
Click to expand...
How do you know in advance that a number should be rounded to 0.001 (and not 0.0011 for example)? Is there a set number of significant digits that you want to see at maximum? If so, how many digits are they.
 
Upvote 0
Rick I think you've hit my problem on the head. I would be happy to just trim the number for one significant digits.

I think I have may have found a way...

these are all less 1, so if I log the number and round that up and then multiple it by -1 to convert the -log10 to a positive number, this number will allow me to define how many points I want to round my original value...

and when I copy and paste this the extra O will disappear.

Something like below

[TABLE="width: 750"]
<tbody>[TR]
[TD="align: center"]0.01
[/TD]
[TD="align: center"]0.00316[/TD]
[TD="align: center"]0.00100[/TD]
[TD="align: center"]0.00032[/TD]
[TD="align: center"]0.00010[/TD]
[TD="align: center"]0.00003[/TD]
[TD="align: center"]0.00001[/TD]
[TD="align: center"]0.00000[/TD]
[TD="align: center"]0.00000[/TD]
[TD="align: center"]Original calculation
[/TD]
[/TR]
[TR]
[TD="align: center"]-2[/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"]-4[/TD]
[TD="align: center"]-4[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]log[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]log * -1[/TD]
[/TR]
[TR]
[TD="align: center"]0.0100000
[/TD]
[TD="align: center"]0.0030000[/TD]
[TD="align: center"]0.0010000[/TD]
[TD="align: center"]0.0003000[/TD]
[TD="align: center"]0.0001000[/TD]
[TD="align: center"]0.0000300[/TD]
[TD="align: center"]0.0000100[/TD]
[TD="align: center"]0.0000030[/TD]
[TD="align: center"]0.0000010[/TD]
[TD="align: center"]Round value[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]0.01[/TD]
[TD="align: center"]0.00300[/TD]
[TD="align: center"]0.00100[/TD]
[TD="align: center"]0.0003[/TD]
[TD="align: center"]0.0001[/TD]
[TD="align: center"]0.00003[/TD]
[TD="align: center"]0.00001[/TD]
[TD="align: center"]0.000003[/TD]
[TD="align: center"]0.000001[/TD]
[TD="align: center"]Copy and paste value
[/TD]
[/TR]
</tbody>[/TABLE]

Work in progress but it might just work.

Comments and thoughts always welcomed.

Many thanks

Hankj
 
Upvote 0
HankJ said:
Rick I think you've hit my problem on the head. I would be happy to just trim the number for one significant digits.

I think I have may have found a way...

these are all less 1, so if I log the number and round that up and then multiple it by -1 to convert the -log10 to a positive number, this number will allow me to define how many points I want to round my original value...

and when I copy and paste this the extra O will disappear.
Click to expand...
Does this do what you want...

=ROUND(A2/500,ROUNDUP(-LOG10(A2/500),0))
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Custom number formatting which limits decimals, but prevents Rounding to zero
Replies
2
Views
369
StrawberryDreams
S
J
  • Question Question
Excel values incorrectly convert to 5 digit number
2 3
Replies
21
Views
3K
Akuini
Akuini
N
  • Question Question
calculate the total time taken to complete a cycle in excel
Replies
5
Views
1K
JustynaMK
JustynaMK
J
  • Question Question
A different type of excel question that may be a yup that is how the data should be laid out...
Replies
1
Views
747
CyrusTheVirus
CyrusTheVirus
M
  • Question Question
Increment a date by a number of days, months or years
2
Replies
16
Views
4K
MrPink1986
M

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top