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 | = | = |
ADVERTISEMENT
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 ?