markusreyes2907
New Member
- Joined
- Jul 14, 2020
- Messages
- 34
- Office Version
- 2013
- Platform
- Windows
I am just wondering if there is another method to write this formula without having to repeat so much of the formula within itself.
The formula is:
Essentially I want to perform a few functions in one quick go, taking an average and standard deviation and having them formatted with the proper amount of significant numbers. Without IF and LEN functions it seems that when an average rounded to 1 is a whole number it only shows up as one digit (ex. 8), but otherwise it will show up as a float (ex. 8.3). I want all numbers to appear as a float and this is the way I ended up providing that functionality. The same goes with the formatting of the standard deviation just with an extra significant number, with the exception I want to add an extra "0" at the end so it will show up as 2.20 and not 2.2 (if the hundredths is not a "0" it would show up with the extra digit [ex. 2.23]).
Thank you.
The formula is:
Excel Formula:
=IF(LEN(ROUND(AVERAGE(A1:A8),1))=1,ROUND(AVERAGE(A1:A8),1)&".0",ROUND(AVERAGE(A1:A8),1))&" ± "&IF(LEN(ROUND(STDEV(A1:A8),2))=3,ROUND(STDEV(A1:A8),2)&"0",ROUND(STDEV(A1:A8),2))
Essentially I want to perform a few functions in one quick go, taking an average and standard deviation and having them formatted with the proper amount of significant numbers. Without IF and LEN functions it seems that when an average rounded to 1 is a whole number it only shows up as one digit (ex. 8), but otherwise it will show up as a float (ex. 8.3). I want all numbers to appear as a float and this is the way I ended up providing that functionality. The same goes with the formatting of the standard deviation just with an extra significant number, with the exception I want to add an extra "0" at the end so it will show up as 2.20 and not 2.2 (if the hundredths is not a "0" it would show up with the extra digit [ex. 2.23]).
Thank you.