format numbers after calculation

[HankJ]

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

 

[HankJ]

Hi Rick

It didn't but I worked on it and got this....

=ROUND(A20/$T$3,(ROUND(LOG10(A20/$T$3),0)*-1))

A20 is the concentration before and T3 is the dilution factor.

And it does it in one action.... lovely stuff.

Many many thanks for keeping with me.

All the best

Hankj

 

[Rick Rothstein]

Hi Rick

It didn't but I worked on it and got this....

=ROUND(A20/$T$3,(ROUND(LOG10(A20/$T$3),0)*-1))

I think if you change the 500 I used in my formula to $T$3 and A2 to A20, my formula should return the same values as the formula you posted above as far as I can tell.

 

[HankJ]

Hi Rick

I'm in the UK and heading home.

I will check that in the morning...

Really good for you to help me.

Wish you a good evening and many thanks

HankJ

 

[HankJ]

I think if you change the 500 I used in my formula to $T$3 and A2 to A20, my formula should return the same values as the formula you posted above as far as I can tell.

Hello Rick

Yes indeed.

Good stuff and many thanks

Best wishes to you

Hankj