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.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Wookiee

Active Member
Joined
Nov 27, 2012
Messages
330
Office Version
  1. 2016
Platform
  1. Windows
Dear Khlee,

You forgot to include your image.

:rolleyes:
 

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

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

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

ADVERTISEMENT

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
330
Office Version
  1. 2016
Platform
  1. Windows
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,962
Messages
5,599,065
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top