|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
Apr 22nd, 2002, 01:30 PM
|
#1 |
|
New Member
Join Date: Apr 2002
Posts: 9
|
can excel round to a certain number of sig figs w/o using the scientific function (i.e 8.47, 203, 42.5) all of those values have 3 sig figs, is there a function that will do this??? thanx for your help!!!
|
|
|
|
Apr 22nd, 2002, 01:43 PM
|
#2 |
|
MrExcel MVP
Join Date: Feb 2002
Location: Columbus, OH, USA
Posts: 3,519
|
I don't have an answer, but have a look at this thread:
http://www.mrexcel.com/board/viewtop...c=5521&forum=2 The last post may be useful. |
|
|
|
|
Apr 22nd, 2002, 01:45 PM
|
#3 |
|
MrExcel MVP
Join Date: Mar 2002
Location: Chicago, IL USA
Posts: 2,042
|
Hi,
This was posted yesterday to your other thread on this issue. Let us know if this will work for you. With your raw data in A1 and the number of significant figures you desire in B1, try: =ROUND(A1,B1-1-INT(LOG10(ABS(A1)))) Bye, Jay |
|
|
|
|
Apr 22nd, 2002, 01:46 PM
|
#4 |
|
MrExcel MVP
Join Date: Feb 2002
Location: Columbus, OH, USA
Posts: 3,519
|
I didn't realise I referred you to yor own thread.
It was Jay's suggestion I was referring to though, so it's in this thread now. |
|
|
|
|
Apr 22nd, 2002, 02:03 PM
|
#5 |
|
New Member
Join Date: Apr 2002
Posts: 9
|
the "<"text($C$24,0.000) did work for my previous question thatnx again!!
|
|
|
|
|
May 20th, 2002, 10:56 AM
|
#6 |
|
New Member
Join Date: Apr 2002
Posts: 9
|
ok, jay's answer works, except for when theres an answer like 0.0468, i believe excel only recognizes numbers other than 0. i need an answer of .047. any help is always appreciated!! thanxs all
|
|
|
|
|
May 20th, 2002, 11:47 AM
|
#7 |
|
MrExcel MVP
Join Date: Mar 2002
Location: Chicago, IL USA
Posts: 2,042
|
Hi,
=ROUND(A1,B1-1-IF(INT(ABS(A1)),0,1)-INT(LOG10(ABS(A1)))) This will not automatically adjust as above for a number like 0.000468, though. Bye, Jay |
|
|
|
|
May 20th, 2002, 12:44 PM
|
#8 |
|
New Member
Join Date: Apr 2002
Posts: 9
|
jay, your first solution worked a little better for me. if i could only use a decimal format with the equation........ its a tough problem, but if you or anyone else can help, its always appreciated!! marc |
|
|
|
|
May 20th, 2002, 01:07 PM
|
#9 |
|
MrExcel MVP
Join Date: Mar 2002
Location: Chicago, IL USA
Posts: 2,042
|
Hi,
How did the first one work better? The amended version was exactly the same as the first if the integer part was not zero. This should have improved the situation somewhat. Are you referring to something like 55.0468 returning 55 rather than 55.0? I don't know any way to work around that unless you format the cell, although you may be able to play with the TEXT function. The problem with the second one returning 0.047 is that it is really only 2 significant figures, not 3. Jay |
|
|
|
|
May 20th, 2002, 01:39 PM
|
#10 |
|
New Member
Join Date: Apr 2002
Posts: 9
|
ok, i got it, by using your 1st equation: With your raw data in A1 and the number of significant figures you desire in B1=3, try: =ROUND(A1,B1-1-INT(LOG10(ABS(A1)))) and adding an additional if statement in another cell, that: With your raw data in A1, the number of significant figures you desire in B2=2 and your previous cell in C3, try: =IF(C3<.1,ROUND(A1,B2-1-INT(LOG10(ABS(A1)))),C3) this takes care of the problem, when numbers are less than .1, and therefore solves the problem. marc really appreciate your help all these times guys!! marc |
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
