# Excel Formula

#### Wookiee

##### Active Member
Dear Khlee,

You forgot to include your image. #### khlee

##### New Member
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).
 A B C D E F G H I J K L M N O P Q 1 24 X 24 = 576 2 3 200 4 2400 X 50 = 120000 5 6 Jan 9600 x 19200 = 28000 = 0.5% >=20000 <=99999 7 Feb 9600 x = 62700 = 0.8% >=100000 <=149999 8 Mar 9600 x = = 1% >=150000 <=199999 9 April 9600 x = = 1.5% >=200000 10 May 9600 x = = 11 Jun 9600 x = = 12 July 9600 x = = 13 Aug 9600 x = = 14 Sep 9600 x = = 15 Oct 9600 x = = 16 Nov 9600 x = = 17 Dec 9600 x = =

<tbody>
</tbody>

Thank you. #### jul stev

##### Board Regular
Hi Khlee,

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.

=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

##### New Member
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.

=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

##### Board Regular
Hi Khlee,

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

##### New Member
Dear Jul Stev,

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

##### New Member
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.

=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

##### Active Member
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.

Code:
``=C6*(\$A\$1/12)*(ROW()-5)``

#### khlee

##### New Member
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 ?  