Excel Query

[TechZ]

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) manually

E1 Ditto

F1 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 manually

H1 Ditto

I1 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 manually

K1 Ditto

L1 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)

FINALLY

M1 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)

[mazin]

Exhaustive help is here: http://www.functionx.com/excel/index.htm

[Carl_Maddox]

Hi

For 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