khlee

New Member
Joined
Jul 15, 2013
Messages
8
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.
 

khlee

New Member
Joined
Jul 15, 2013
Messages
8
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).
ABCDEFGHIJKLMNOPQ
124X24=576
2
3200
42400X50=120000
5
6Jan9600x19200=28000=0.5%>=20000<=99999
7Feb9600x=62700=0.8%>=100000<=149999
8Mar9600x==1%>=150000<=199999
9April9600x==1.5%>=200000
10May9600x==
11Jun9600x==
12July9600x==
13Aug9600x==
14Sep9600x==
15Oct9600x==
16Nov9600x==
17Dec9600x==

<tbody>
</tbody>

Please advice.

Thank you.
:rolleyes:
 

jul stev

Board Regular
Joined
Jun 26, 2013
Messages
51
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

New Member
Joined
Jul 15, 2013
Messages
8
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

Board Regular
Joined
Jun 26, 2013
Messages
51
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

New Member
Joined
Jul 15, 2013
Messages
8
Dear Jul Stev,

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

khlee

New Member
Joined
Jul 15, 2013
Messages
8
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

Active Member
Joined
Nov 27, 2012
Messages
309
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
Joined
Jul 15, 2013
Messages
8
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 ? :confused::eek:
 

Forum statistics

Threads
1,085,842
Messages
5,386,292
Members
401,992
Latest member
CleverHopper

Some videos you may like

This Week's Hot Topics

Top