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

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>

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]

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 ?

 

[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]

ADVERTISEMENT

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 ?