How to solve this problem? With nested ifs or vlookups?

raven_ramon · Jan 5, 2011

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5Cariela%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5Cariela%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5Cariela%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-520092929 1073786111 9 0 415 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0cm; margin-right:0cm; margin-bottom:10.0pt; margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} a:link, span.MsoHyperlink {mso-style-noshow:yes; mso-style-priority:99; color:blue; text-decoration:underline; text-underline:single;} a:visited, span.MsoHyperlinkFollowed {mso-style-noshow:yes; mso-style-priority:99; color

urple; mso-themecolor:followedhyperlink; text-decoration:underline; text-underline:single;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page WordSection1 {size:612.0pt 792.0pt; margin:72.0pt 72.0pt 72.0pt 72.0pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> </style> Hello,
I am trying to create a calculation that will provide the following:

Not sure how to upload a picture here, but i posted it on a temporary blog here: http://ravenexcel.blogspot.com/2011/01/help-on-mr-excel.html

What i want to be able to do is that if an X appears in the X row (excel line 6), then in excel line 9 (the yellow line), it will appear auto calculations - in the first month where the X is, it will be 10% of the amount in cell B9, then 100%, then another 100%, then sliding by 10% each month.

The end result should be like the green line.

Not sure how to do it. Started with nested if, but it didnt work.
Can someone kindly suggest a solution?
Thank you in advance.

crook_101 · Jan 5, 2011

HI,

Think this is what you are looking for:

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

4

Jan-11

Feb-11

Mar-11

Apr-11

May-11

Jun-11

Jul-11

Aug-11

Sep-11

Oct-11

Nov-11

Dec-11

Jan-12

5

6

Release Month

Feb-11

x

7

9000000

8

2%

9

Implied Sales

180000

10

11

Capture Rate

12

Month 1

10%

13

Month 2

100%

14

Month 3

100%

15

Month 4

90%

16

Month 5

80%

17

Month 6

70%

18

Month 7

60%

19

Month 8

50%

20

Month 9

40%

21

Month 10

30%

22

Month 11

20%

23

Month 12

10%

Sheet1

Cheers,
Ian

raven_ramon · Jan 5, 2011

Hi Ian,

Thanks for the quick reply, but that is not quite what i had in mind.

Your solutions will only work if the X appears in the Original cell of E6.

What will happen if the X will appear in Cell H6? Then i have to copy all the formulas again manually, correct? (i sohuld have mentioned that the month in cell B6 determines where the X will appear).

What i would like it to do is to calculate the formulas automatically in what ever column it will appear (i.e D6....zz6 etc...).

Thank you.

crook_101 · Jan 5, 2011

Hi Raven

Apologies for the delay - I had to do some "home" stuff with the other half. Anyway, I have found a solution for you, not too elegant but it will work. We need to add some rows to your sheet, feel free to hide themonce you have completed the copy/paste but they need to be there for the formulea to work.

Excel Workbook

B

C

D

E

3

4

Jan-11

Feb-11

5

6

Release Month

Feb-11

x

7

9000000

8

2%

9

Implied Sales

180000

18000

180000

10

11

Capture Rate

12

Month 1

10%

13

Month 2

100%

14

Month 3

100%

15

Month 4

90%

16

Month 5

80%

17

Month 6

70%

18

Month 7

60%

19

Month 8

50%

20

Month 9

40%

21

Month 10

30%

22

Month 11

20%

23

Month 12

10%

24

Month 1

Month 2

25

26

27

28

29

Month 2

Month 1

30

Month 3

Month 2

31

Month 4

Month 3

32

Month 5

Month 4

33

Month 6

Month 5

34

Month 7

Month 6

35

Month 8

Month 7

36

Month 9

Month 8

37

Month 10

Month 9

38

Month 11

Month 10

39

Month 12

Month 11

40

Month 13

Month 12

Sheet1

Copy and paste the cells B29:C40 and paste them in the same cell id on your sheet

Copy and paste the formula as shown above into the same cell id on your sheet

Once you have pasted E9 drag these formula across to row P, then do the same with the formula in E24 - DO NOT drag the D9 and D24 anywhere!!

Any problems, post back and I will pick this up again tomorrow

Hope this resolves??

Ian.

Peter_SSs · Jan 5, 2011

Welcome to the MrExcel board!

raven_ramon said:
Not sure how to upload a picture here, ...

See my signature block below for 3 methods, all of which are better than posting an image elsewhere because ..

a) Many helpers here will not bother to track to another site to even look, and
b) Your sample data cannot be copied from that image to a test sheet whereas using the methods suggested below it can be. Most helpers don't much like manually typing sample data.

For your problem, try this. I have used an extra helper cell, the green one. Formula in D9 is just copied across.

Excel Workbook

A

B

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

4

Jan-11

Feb-11

Mar-11

Apr-11

May-11

Jun-11

Jul-11

Aug-11

Sep-11

Oct-11

Nov-11

Dec-11

Jan-12

Feb-12

Mar-12

5

6

Release Month

Feb-11

x

7

9000000

8

2%

9

Implied Sales

180000

18000

180000

162000

144000

126000

108000

90000

72000

54000

36000

18000

10

1

11

Capture Rate

12

Month 1

10%

13

Month 2

100%

14

Month 3

100%

15

Month 4

90%

16

Month 5

80%

17

Month 6

70%

18

Month 7

60%

19

Month 8

50%

20

Month 9

40%

21

Month 10

30%

22

Month 11

20%

23

Month 12

10%

raven_ramon

crook_101 · Jan 5, 2011

Peter,

Nice

Cheers,
Ian

Peter_SSs · Jan 5, 2011

BTW, if the only job of the 'x' cell is to mark the start of these numbers, then the formulas across row 6 that insert the 'x' could be removed and change my B10 formula to:

=MATCH(B6,D4:S4)-1

.. adjusting the 'S' column reference to whatever the last date column in row 4 is.

raven_ramon · Jan 6, 2011

Thank you both for the help.
I will try these later on today and post back if any issues.
Thanks again. Raven.

raven_ramon · Jan 6, 2011

Peter and Ian, Thank you again both for your help.

Peter's response worked after i adjusted the formula in D10 to your last suggestion.
Ian, - i guess i got lost with all the formulas and the various columns when trying to put it in the actual workbook i am using.

Thanks again for all your help. G-d Bless.

Peter_SSs · Jan 6, 2011

Glad it worked. Just looking at this again, those formulas could be simplied considerably if some more helper cells were used.

My original B10 formula is moved to A3.
New formula in B3.
D3 and E3 were entered manually then selected and dragged across to fill the yellow section.
Row 3 could then be hidden.
Simpler formula for D9, copied across.

Excel Workbook

A

B

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

3

1

12

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

4

Jan-11

Feb-11

Mar-11

Apr-11

May-11

Jun-11

Jul-11

Aug-11

Sep-11

Oct-11

Nov-11

Dec-11

Jan-12

Feb-12

Mar-12

Apr-12

5

6

Release Month

Feb-11

7

9000000

8

2%

9

Implied Sales

180000

18000

180000

162000

144000

126000

108000

90000

72000

54000

36000

18000

10

11

Capture Rate

12

Month 1

10%

13

Month 2

100%

14

Month 3

100%

15

Month 4

90%

16

Month 5

80%

17

Month 6

70%

18

Month 7

60%

19

Month 8

50%

20

Month 9

40%

21

Month 10

30%

22

Month 11

20%

23

Month 12

10%

raven_ramon

How to solve this problem? With nested ifs or vlookups?

raven_ramon

New Member

Excel Facts

crook_101

Well-known Member

raven_ramon

New Member

crook_101

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

crook_101

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

raven_ramon

New Member

raven_ramon

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

How to solve this problem? With nested ifs or vlookups?

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

New Member

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock