Home |
Search |
Today's Posts |
#1
|
|||
|
|||
a function on decimals
Hello,
would someone be able to help me build a function that would take the last two digits in a value compare it then return a '0' or '5'. For example, Column A 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) -- take last two digits '38' compared to .00025. If it's less than .00025 then those two last digits should become a 0 if greater than .00025 then it should be '5'. RESULT 1.5315 --------- 1.53047 -- 1.5305 1.53002 -- 1.5300 1.52957 -- 1.5295 Thank you! |
#2
|
|||
|
|||
Try
=ROUND((A1*100000)/25,0)*25/100000 "chartasap" wrote in message ... Hello, would someone be able to help me build a function that would take the last two digits in a value compare it then return a '0' or '5'. For example, Column A 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) -- take last two digits '38' compared to .00025. If it's less than .00025 then those two last digits should become a 0 if greater than .00025 then it should be '5'. RESULT 1.5315 --------- 1.53047 -- 1.5305 1.53002 -- 1.5300 1.52957 -- 1.5295 Thank you! |
#3
|
|||
|
|||
For a value in cell A1:
=IF(A1*1000-INT(A1*1000)=0.25,INT(A1*1000)/1000+0.0005,INT(A1*1000)/1000) You didn't say what to do it it were equal to 25, so I chose =, but you can change that. HTH, Bernie MS Excel MVP "chartasap" wrote in message ... Hello, would someone be able to help me build a function that would take the last two digits in a value compare it then return a '0' or '5'. For example, Column A 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) -- take last two digits '38' compared to .00025. If it's less than .00025 then those two last digits should become a 0 if greater than .00025 then it should be '5'. RESULT 1.5315 --------- 1.53047 -- 1.5305 1.53002 -- 1.5300 1.52957 -- 1.5295 Thank you! |
#4
|
|||
|
|||
One more alternative
=TRUNC(A1,3)+0.0005*(MID(A1,SEARCH(".",A1)+4,2)25 ) "chartasap" wrote: Hello, would someone be able to help me build a function that would take the last two digits in a value compare it then return a '0' or '5'. For example, Column A 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) -- take last two digits '38' compared to .00025. If it's less than .00025 then those two last digits should become a 0 if greater than .00025 then it should be '5'. RESULT 1.5315 --------- 1.53047 -- 1.5305 1.53002 -- 1.5300 1.52957 -- 1.5295 Thank you! |
#5
|
|||
|
|||
Oops - make that
=TRUNC(A1,3)+0.0005*(VALUE(MID(A1,SEARCH(".",A1)+4 ,2))25) "chartasap" wrote: Hello, would someone be able to help me build a function that would take the last two digits in a value compare it then return a '0' or '5'. For example, Column A 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) -- take last two digits '38' compared to .00025. If it's less than .00025 then those two last digits should become a 0 if greater than .00025 then it should be '5'. RESULT 1.5315 --------- 1.53047 -- 1.5305 1.53002 -- 1.5300 1.52957 -- 1.5295 Thank you! |
#6
|
|||
|
|||
Barb,
It works great however, when i used the function below for values such as 1.52866, 1.52821, 1.52775, 1.52730 etc. the result is not correct. Any idea why it won't work on these? "Barb Reinhardt" wrote: Try =ROUND((A1*100000)/25,0)*25/100000 "chartasap" wrote in message ... Hello, would someone be able to help me build a function that would take the last two digits in a value compare it then return a '0' or '5'. For example, Column A 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) -- take last two digits '38' compared to .00025. If it's less than .00025 then those two last digits should become a 0 if greater than .00025 then it should be '5'. RESULT 1.5315 --------- 1.53047 -- 1.5305 1.53002 -- 1.5300 1.52957 -- 1.5295 Thank you! |
#7
|
|||
|
|||
Thank you Bernie...i tried this formula and it works wonderfully. Thank you
for your time. "Bernie Deitrick" wrote: For a value in cell A1: =IF(A1*1000-INT(A1*1000)=0.25,INT(A1*1000)/1000+0.0005,INT(A1*1000)/1000) You didn't say what to do it it were equal to 25, so I chose =, but you can change that. HTH, Bernie MS Excel MVP "chartasap" wrote in message ... Hello, would someone be able to help me build a function that would take the last two digits in a value compare it then return a '0' or '5'. For example, Column A 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) -- take last two digits '38' compared to .00025. If it's less than .00025 then those two last digits should become a 0 if greater than .00025 then it should be '5'. RESULT 1.5315 --------- 1.53047 -- 1.5305 1.53002 -- 1.5300 1.52957 -- 1.5295 Thank you! |
#8
|
|||
|
|||
Thank you Duke...this is definitely another way i can go.
"Duke Carey" wrote: One more alternative =TRUNC(A1,3)+0.0005*(MID(A1,SEARCH(".",A1)+4,2)25 ) "chartasap" wrote: Hello, would someone be able to help me build a function that would take the last two digits in a value compare it then return a '0' or '5'. For example, Column A 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) -- take last two digits '38' compared to .00025. If it's less than .00025 then those two last digits should become a 0 if greater than .00025 then it should be '5'. RESULT 1.5315 --------- 1.53047 -- 1.5305 1.53002 -- 1.5300 1.52957 -- 1.5295 Thank you! |
#9
|
|||
|
|||
Because it is rounding to the nearest .00025, not as per your actual criteria.
HTH, Bernie MS Excel MVP It works great however, when i used the function below for values such as 1.52866, 1.52821, 1.52775, 1.52730 etc. the result is not correct. Any idea why it won't work on these? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |