TechZ Posted October 30, 2004 Share Posted October 30, 2004 I need help in an MS Excel formula(its for my mom)Heres how it is supposed to work:D1 Amount is entered(26.35) manuallyE1 DittoF1 AVERAGE is calculated of D1 & E1 and then the number is evaluated such that if the decimal portion is a) between .00 and .34, it is removed (46.20-->46) B ) between .35 and .69, it is made 0.50 (46.55-->46.5) c) between .70 and .99, it is made to go to the next number (46.80-->47)G1 Amount is entered manuallyH1 DittoI1 AVERAGE is calculated of G1 & H1 and then the number is evaluated such that if the decimal portion is a) between .00 and .34, it is removed (46.20-->46) B ) between .35 and .69, it is made 0.50 (46.55-->46.5) c) between .70 and .99, it is made to go to the next number (46.80-->47)J1 Amount is entered manuallyK1 DittoL1 AVERAGE is calculated of J1 & K1 and then the number is evaluated such that if the decimal portion is a) between .00 and .34, it is removed (46.20-->46) B ) between .35 and .69, it is made 0.50 (46.55-->46.5) c) between .70 and .99, it is made to go to the next number (46.80-->47)FINALLYM1 The Three Averages of F1,I1,L1 are added and again evaluated such that if the decimal portion is a) between .00 and .34, it is removed (46.20-->46) B ) between .35 and .69, it is made 0.50 (46.55-->46.5) c) between .70 and .99, it is made to go to the next number (46.80-->47) Link to comment Share on other sites More sharing options...
mazin Posted October 30, 2004 Share Posted October 30, 2004 Exhaustive help is here: http://www.functionx.com/excel/index.htm Link to comment Share on other sites More sharing options...
Carl_Maddox Posted May 31, 2005 Share Posted May 31, 2005 HiFor the first calculation just use :IF(MOD(AVERAGE(D1,E1),2)<=0.34,INT(AVERAGE(D1,E1)),IF(MOD(AVERAGE(D1,E1),2)<=0.69,SUM(INT(AVERAGE(D1,E1)),0.5),ROUNDUP(AVERAGE(D1,E1),0)))Substitute the cells (D1, E1) for the new cell addresses as you go along.You may also wish to reference your targets (i.e. 0.34, 0.69) through lookups for ease of use.Cheers,Carl Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now