Excel Formula

[khlee]

Dear all,

Did any one can teach me how to set this difficult formula in excel cell ?
Please refer to the image as below.



Thank you.

 

[Wookiee]

Dear Khlee,

You forgot to include your image.

 

[khlee]

Dear Wookiee,

Please find below to solve this difficult formula.
1. For (E6 - E17), I need to get average of (A1) devided by 12 months to add in Jan until December, mean every months add in 2*9600=19200. For example on E6, I manage to get the answer =C6*(A1/12), but the rest I don't get the answer.

2.Base on total of G6, I need excel to multiple sum based on table % from (K6) to (K10); sum range for 0.5% is (>=20000 to <=99999), could it be possible ?

*A1 & A3 was inserted formula. Once number was edit to others SUM of (E1), (E4) & (C6) answer will be change. (C6) formula is (A4*4).
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]24[/TD]
[TD]X[/TD]
[TD]24[/TD]
[TD]=[/TD]
[TD]576[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2400[/TD]
[TD]X[/TD]
[TD]50[/TD]
[TD]=[/TD]
[TD]120000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jan[/TD]
[TD][/TD]
[TD]9600[/TD]
[TD]x[/TD]
[TD]19200[/TD]
[TD]=[/TD]
[TD]28000[/TD]
[TD]=[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.5%[/TD]
[TD]>=20000[/TD]
[TD]<=99999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Feb[/TD]
[TD][/TD]
[TD]9600[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]=[/TD]
[TD]62700[/TD]
[TD]=[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.8%[/TD]
[TD]>=100000[/TD]
[TD]<=149999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Mar[/TD]
[TD][/TD]
[TD]9600[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD][/TD]
[TD]1%[/TD]
[TD]>=150000[/TD]
[TD]<=199999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]April[/TD]
[TD][/TD]
[TD]9600[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD][/TD]
[TD]1.5%[/TD]
[TD]>=200000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]May[/TD]
[TD][/TD]
[TD]9600[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Jun[/TD]
[TD][/TD]
[TD]9600[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]July[/TD]
[TD][/TD]
[TD]9600[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Aug[/TD]
[TD][/TD]
[TD]9600[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Sep[/TD]
[TD][/TD]
[TD]9600[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Oct[/TD]
[TD][/TD]
[TD]9600[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Nov[/TD]
[TD][/TD]
[TD]9600[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Dec[/TD]
[TD][/TD]
[TD]9600[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD]=[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Please advice.

Thank you.

​

 

[jul stev]

Hi Khlee,

Hope i understand clearly about your problem, this is my answer :

1.For (E6 - E17), I need to get average of (A1) devided by 12 months to add in Jan until December, mean every months add in 2*9600=19200. For example on E6, I manage to get the answer =C6*(A1/12), but the rest I don't get the answer.

Answer : Your formula ( =C6*(A1/12) ) is correct but you didnt get the answer as you copy it down because you didnt make it as an absolute reference, try =C6*($A$1/12) and copy it down. To make absolute reference, the shortcut is F4.

2. Base on total of G6, I need excel to multiple sum based on table % from (K6) to (K10); sum range for 0.5% is (>=20000 to <=99999), could it be possible ?
Answer : Change your reference table into this and put it at K6
- -
20,000 0.50%
100,000 0.80%
150,000 1%
200,000 1.50%

In I6 put this formula : =G6+(G6*LOOKUP(G6,$K$6:$K$10,$L$6:$L$10))

Hope this help

 

[khlee]

Dear Jul Stev,

Thank you for replied. Sorry if i giving wrong info and question.

1.For (E6 - E17), I need to get average of (A1) devided by 12 months to add in Jan until December, mean every months add in 2*9600=19200. For example on E6, I manage to get the answer =C6*(A1/12), but the rest I don't get the answer.

Answer : Your formula ( =C6*(A1/12) ) is correct but you didnt get the answer as you copy it down because you didnt make it as an absolute reference, try =C6*($A$1/12) and copy it down. To make absolute reference, the shortcut is F4.
For the first question. If (A1) = 24, what i want is from Jan add in 2*C6, Feb add in 4*C7, March add in 6*C8, until Dec add in 24*C17 by itself. If I insert 36 in (A1), will excel have any formula will help me arrange add in by Jan 3*C6, Feb 6*C7, March 9*C8....until Dec 36*C17 itself ?

2. Base on total of G6, I need excel to multiple sum based on table % from (K6) to (K10); sum range for 0.5% is (>=20000 to <=99999), could it be possible ?
Answer : Change your reference table into this and put it at K6
- -
20,000 0.50%
100,000 0.80%
150,000 1%
200,000 1.50%

In I6 put this formula : =G6+(G6*LOOKUP(G6,$K$6:$K$10,$L$6:$L$10))
For this question, if the total of G6 is >=20000 and <=99999, it will direct multiply 0.5%; >=100000 and <=149999, will multiply 0.8%.
For example, G6 = 28800 mean (>=20000 and <=99999), it only multiply 0.5% = 144.

By these 2 question, is it quite difficult to put in formula ?

 

[jul stev]

Hi Khlee,

Im afraid that i cant help you solve the 1st problem, hope the other members can help you through that,
and for the 2nd problem i cant find any problem with it, if you want 144 show up in G6 you just need to delete the 1st part of my formula ( G6+ ) and im sure it will return 144 if the value in G6 = 28800

 

[khlee]

Dear Jul Stev,

Thank you for your replied. I got the 2nd question already.

 

[khlee]

Dear Jul Stev,

Thank you for replied. Sorry if i giving wrong info and question.

1.For (E6 - E17), I need to get average of (A1) devided by 12 months to add in Jan until December, mean every months add in 2*9600=19200. For example on E6, I manage to get the answer =C6*(A1/12), but the rest I don't get the answer.

Answer : Your formula ( =C6*(A1/12) ) is correct but you didnt get the answer as you copy it down because you didnt make it as an absolute reference, try =C6*($A$1/12) and copy it down. To make absolute reference, the shortcut is F4.
For the first question. If (A1) = 24, what i want is from Jan add in 2*C6, Feb add in 4*C7, March add in 6*C8, until Dec add in 24*C17 by itself. If I insert 36 in (A1), will excel have any formula will help me arrange add in by Jan 3*C6, Feb 6*C7, March 9*C8....until Dec 36*C17 itself ?

2. Base on total of G6, I need excel to multiple sum based on table % from (K6) to (K10); sum range for 0.5% is (>=20000 to <=99999), could it be possible ?
Answer : Change your reference table into this and put it at K6
- -
20,000 0.50%
100,000 0.80%
150,000 1%
200,000 1.50%

In I6 put this formula : =G6+(G6*LOOKUP(G6,$K$6:$K$10,$L$6:$L$10))
For this question, if the total of G6 is >=20000 and <=99999, it will direct multiply 0.5%; >=100000 and <=149999, will multiply 0.8%.
For example, G6 = 28800 mean (>=20000 and <=99999), it only multiply 0.5% = 144.

By these 2 question, is it quite difficult to put in formula ?

Anyone still can help me on the 1st problem in RED ?

 

[Wookiee]

I think that this formula does what you want. If you place it in E6 and copy it down, it shoud provide the results based on the value in cell A1.

=C6*($A$1/12)*(ROW()-5)

 

[khlee]

Dear Wookiee,

Thank. But with your formula, *(ROW()-5) mean ? I did copy to cell, but it not get the actual answer. E6 answer suppose is 19200.
Anyway, this is the actual question as below:-
For the first question. If (A1) = 24, what i want is from Jan add in 2*C6, Feb add in 4*C7, March add in 6*C8, until Dec add in 24*C17 by itself. If I insert 36 in (A1), will excel have any formula will help me arrange add in by Jan 3*C6, Feb 6*C7, March 9*C8....until Dec 36*C17 itself ?
Is it difficult to set the formula ? If yes any others solution ?