formula to find categories ratio & numbers

```Hello

Well.... of cource there is a manual timing way for my question but I
require a more smart way or a formula for it.

We have a Monthly Forcast which is broken to 4 category & in the other hand
we have a Total Master Plan Requirements. Based om Monthly Forcast we can
find ratio of each category to others & then multiply it with Total Master
Plan requirements to calculate "What should be each category to achieve Total
Master Plan requirements?"

Exp:
months         Jan  Forcast        Feb Forcast       Plan
Category 1        A                         C                   ?
Category 2        B                          D                  ?
Category 3         E                         F                   ?
Category 4        G                         H                   ?
Sum                 A+B+E+G          C+D+E+F        ?
Master Plan      M                        K                    J

It is clear that with good job we can reach    A+B+E+G= M otherwise we are
behind M and so on... again the question is "What should be each category to
achieve Total Master Plan requirements?"

I am not sure if I explan it clear. but this question is a chalenge for me?

Bahareh

```
 0
Utf
1/23/2008 1:55:01 PM
access 16762 articles. 3 followers.

3 Replies
474 Views

Similar Articles

[PageSpeed] 55

```Bahareh:

For January Cat 1:  	A/(A+B+E+G)*M
For January Cat 2:  	B/(A+B+E+G)*M
For January Cat 3:  	E/(A+B+E+G)*M
For January Cat 4:  	G/(A+B+E+G)*M

For February Cat 1:  	C/(C+D+F+H)*K
For February Cat 2:  	D/(C+D+F+H)*K
For February Cat 3:  	F/(C+D+F+H)*K
For February Cat 4:  	H/(C+D+F+H)*K

Ken Sheridan
Stafford, England

"Bahareh" wrote:

> Hello
>
> Well.... of cource there is a manual timing way for my question but I
> require a more smart way or a formula for it.
>
> We have a Monthly Forcast which is broken to 4 category & in the other hand
> we have a Total Master Plan Requirements. Based om Monthly Forcast we can
> find ratio of each category to others & then multiply it with Total Master
> Plan requirements to calculate "What should be each category to achieve Total
> Master Plan requirements?"
>
> Exp:
> months         Jan  Forcast        Feb Forcast       Plan
> Category 1        A                         C                   ?
> Category 2        B                          D                  ?
> Category 3         E                         F                   ?
> Category 4        G                         H                   ?
> Sum                 A+B+E+G          C+D+E+F        ?
> Master Plan      M                        K                    J
>
> It is clear that with good job we can reach    A+B+E+G= M otherwise we are
> behind M and so on... again the question is "What should be each category to
> achieve Total Master Plan requirements?"
>
>
> I am not sure if I explan it clear. but this question is a chalenge for me?
>
> Bahareh
>
>

```
 0
Utf
1/23/2008 5:57:02 PM
```Thanks Ken. I changed the layout of the info so I could use this simple
formula.
sometimes minds block!

Thanks
Bahareh

"Ken Sheridan" wrote:

> Bahareh:
>
> For January Cat 1:  	A/(A+B+E+G)*M
> For January Cat 2:  	B/(A+B+E+G)*M
> For January Cat 3:  	E/(A+B+E+G)*M
> For January Cat 4:  	G/(A+B+E+G)*M
>
> For February Cat 1:  	C/(C+D+F+H)*K
> For February Cat 2:  	D/(C+D+F+H)*K
> For February Cat 3:  	F/(C+D+F+H)*K
> For February Cat 4:  	H/(C+D+F+H)*K
>
> Ken Sheridan
> Stafford, England
>
> "Bahareh" wrote:
>
> > Hello
> >
> > Well.... of cource there is a manual timing way for my question but I
> > require a more smart way or a formula for it.
> >
> > We have a Monthly Forcast which is broken to 4 category & in the other hand
> > we have a Total Master Plan Requirements. Based om Monthly Forcast we can
> > find ratio of each category to others & then multiply it with Total Master
> > Plan requirements to calculate "What should be each category to achieve Total
> > Master Plan requirements?"
> >
> > Exp:
> > months         Jan  Forcast        Feb Forcast       Plan
> > Category 1        A                         C                   ?
> > Category 2        B                          D                  ?
> > Category 3         E                         F                   ?
> > Category 4        G                         H                   ?
> > Sum                 A+B+E+G          C+D+E+F        ?
> > Master Plan      M                        K                    J
> >
> > It is clear that with good job we can reach    A+B+E+G= M otherwise we are
> > behind M and so on... again the question is "What should be each category to
> > achieve Total Master Plan requirements?"
> >
> >
> > I am not sure if I explan it clear. but this question is a chalenge for me?
> >
> > Bahareh
> >
> >
>
```
 0
Utf
1/27/2008 7:13:01 AM
```Thanks Ken. I changed the layout of info so I could use this simple formula.
sometimes mind blocks!

Thanks
Bahareh

"Ken Sheridan" wrote:

> Bahareh:
>
> For January Cat 1:  	A/(A+B+E+G)*M
> For January Cat 2:  	B/(A+B+E+G)*M
> For January Cat 3:  	E/(A+B+E+G)*M
> For January Cat 4:  	G/(A+B+E+G)*M
>
> For February Cat 1:  	C/(C+D+F+H)*K
> For February Cat 2:  	D/(C+D+F+H)*K
> For February Cat 3:  	F/(C+D+F+H)*K
> For February Cat 4:  	H/(C+D+F+H)*K
>
> Ken Sheridan
> Stafford, England
>
> "Bahareh" wrote:
>
> > Hello
> >
> > Well.... of cource there is a manual timing way for my question but I
> > require a more smart way or a formula for it.
> >
> > We have a Monthly Forcast which is broken to 4 category & in the other hand
> > we have a Total Master Plan Requirements. Based om Monthly Forcast we can
> > find ratio of each category to others & then multiply it with Total Master
> > Plan requirements to calculate "What should be each category to achieve Total
> > Master Plan requirements?"
> >
> > Exp:
> > months         Jan  Forcast        Feb Forcast       Plan
> > Category 1        A                         C                   ?
> > Category 2        B                          D                  ?
> > Category 3         E                         F                   ?
> > Category 4        G                         H                   ?
> > Sum                 A+B+E+G          C+D+E+F        ?
> > Master Plan      M                        K                    J
> >
> > It is clear that with good job we can reach    A+B+E+G= M otherwise we are
> > behind M and so on... again the question is "What should be each category to
> > achieve Total Master Plan requirements?"
> >
> >
> > I am not sure if I explan it clear. but this question is a chalenge for me?
> >
> > Bahareh
> >
> >
>
```
 0
Utf
1/27/2008 7:15:01 AM

Similar Artilces:

Clear Contents But Not Formula
I want to use a complicated worksheet that was devised for last year but contents will now change to this year. How can I clear the contents of the cells, the numbers, but leave the formula remaining? Hi click in the sheet you want to do this in (please try on a copy of your workbook first), choose edit / goto, click the special button and then check "constants" .. there's some tick boxes you can play with too ... then click OK and press the delete key. Hope this helps Cheers JulieD "Gancom3" <Gancom3@discussions.microsoft.com> wrote in message news:8749...

How do you add text after a formula?
I'm working on creating a report. At the top of each section is a mont that I type in. At the bottom I want a cell to display that month an also add the word "Total". So for example I have May listed in Cell B3 I now want "May Total" to be listed in cell B36. I know if I want it t display just "May" I'd enter =B3 but how do I make it add the Tota part? Thanks in advance for any help -- Wease ----------------------------------------------------------------------- Weasel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2720 Vie...

Find Discontinued Number
I have a spredsheet to a few columns and one of the field is check numbers. This column looks like this: Check # 101 102 103 106 107 109 110 .... 120 I want to find out which numbers are missing between 101 and 120. The result I want should be:104, 105,108... Could anybody help? Plumstone, There may be better ways of doing it but try: Sub tester() Dim x As Long Dim y As Long Dim n As Long Dim EndCell As Long Dim StartCell As Long Dim StartCol As Integer StartCell = 9 StartCol = 3 EndCell = Cells(Rows.Count, StartCol).End(xlUp).Row n = StartCell ...

Finding the currently selected cell in another worksheet
Hi, In Excel 2002 I want to display the current row of worksheet A in worksheet B (in another format). Does anyone know how to do this? Ideal would be if this updates automatically, but performance wise I guess a macro activated by a button in worksheet A would be the best solution. TBH, Jeroen Jeroen, I don't know of a way in a formula to determine the active cell of the worksheet. Worksheets other than the active one don't have an active cell. I think macros are the only way. Put this in a regular module: Public Roww As Long Put this in the sheet A module: Private Sub W...

Dates in Formulae
Dear all, Suppose I want to create a basic formula eg one which adds 6 months to a date, how would I do it? Doing it by date + 6 months worth of days will obviously not work as the number of days in 6 months will vary. Any thoughts? Thanks, Danny Hi Danny, =DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)) But do think about what you want the day to be in case the source date is, for example, august 31. -- Kind Regards, Niek Otten Microsoft MVP - Excel "Aardvark" <djurmann@hotmail.com> wrote in message news:%23cU8IWF0DHA.2580@TK2MSFTNGP09.phx.gbl... > Dear all, > > ...

Formulas for race game (position calculation after each round)
Hi. I have a worksheet like the following: Player's Name-----Round1(R1)-----R2-----R3...-----Total Score====Position after R1-----Pos.(R2)-----Pos.(R3)... Position is calculated based on the score accumalted (the more scores, the better) so far. Eg: Postion after R1 depends on score got in R1. Position after R2 depends on score got in R1 & R2. Position after R3 depends on score got in R1 & R2 & R3. Q: What formulas should I use to calculate the positions up to different rounds? I think I can use RANK functions, but it seems I need to set SUM formulas to calculate total scores...

Formula for calculating Gross Profit with muktiple discounts #2
List Price less Disc1 less Disc2 equals Net Price. Sell price less net price equal profit divided by sell price equal gross margin percentage. \$10 less 50% less 10% =\$4.50. Sell for \$12.50. \$6.00 less \$4.50 =\$1.50. \$1.50/6.00 = 25% GP. All of these figures are in cells of their own. Cannot get this to calculate correcly. Please help! Urgent CARM Wrote: > List Price less Disc1 less Disc2 equals Net Price. Sell price less net > price equal profit divided by sell price equal gross margi > percentage. > > \$10 less 50% less 10% =\$4.50. Sell for \$12.50. \$6.00 less > \$4....

Set Number of Previous Documents under FILE
When I open Excel and click on FILE on the menu bar, it only shows 4 previously opened documents. I want it to show 8-10 but can't find any options to change it. Can it be done? I have the student and teacher edition 2003. <Tools> <Options> <General> tab, Change number of entries by the "RecentlyUsedFileList". -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Carolyn" <anonymous@discussions.microsoft.com> wrote i...

Create Formula
I need to create a formula where I add a set number of Networkdays to a start date. Example: Start Date = 1/2/07 Number of Networkdays = 21 End Date = Is calculated If Start = 1/2/07 and 21 Networkdays are added, what is the retured end date???? I can only find example of Networkdays where I would be providing the Start and End Dates, and it will calculate the Networkdays. I can't find example where End Date is calculated based on the number of networkdays from start date. Can anyone help with a valid formula for this? Thanks!! ...

New "Card Vault" retrieves card numbers in version 1.3
We have released a very useful application that I feel anyone could benefit from. With version 1.3 the credit cards are all masked, there is no way around this. They are also deleted from the batch table after the settlement so they cannot be retrieved at a future time. Before version 1.2 if there was a problem settling the batch you could just change the status in the visanetauthorization table and re-batch, this is not possible with version 1.3. If this ever happens with the new version you have to call the bank and they hopefully have the credit card number, you then have to re-ring in a tr...

Excel Formula Error for No Good Reason
I don't know why, but this formula will not stop giving me an error in Excel 2003. =IF( \$A8 <> "", B8 * E8, IF( ROW(H7) <> 1, IF( G8 = "Subtotal", SUM( H\$2:H7 ), IF( LEFT(G8, 3) = "Tax", ROUND( \$J\$1 * H7, 2), IF( G8 = "Total", INDIRECT( ADDRESS( ROW(H8)-2, COLUMN(H8) ) ) + H7, IF( G8 = "Depr", SUM( INDIRECT( "G2:"&ADDRESS( ROW(H8) - 4, COLUMN(G7),4) ,TRUE) ), "" ) ...

Changing formula in multiple cells or range simultaneously
I am trying to change the value in multiple cells in a large worksheet simultaneuously. I want to identify the range and then adjust the formula in the entire range. Is there a way that I can highlight the range and then change to formula in each, simultaneously? For example, if I wanted to double the value in the entire range, how would I do this? Thanks, Michael You could put 2 in an empty cell. Copy it Edit|Paste special|click on Multiply under the operation section. Then clear out that 2. But it really depends on what kind of change you're making. If you wanted to ad...

excel formula.
=IF(Sheet1!B4=Sheet1!B1,2,If(Sheet1!B4>Sheet1!B1,1,If (Sheet1!B4<Sheet1!B1,4) In the above formula how would I insert an incremental amount. ie: If(Sheet1!B4>Sheet1!B1,1, the increase to be by two and the result remain one. ie: (Sheet1!B4<Sheet1!B1,4) the decrease be by 1 with the result remaining 4 I am sure that what you want to do is possible, but you have to be a little more descriptive to make us understand. Thanks. Ideally, put up some cell names, put values, then say what you want done. Boris >-----Original Message----- >=IF(Sheet1!B4=Sheet1!...

formula
Hi, I want to copy A1, A3, A5, A7 etc. into a seperate column, but when I try to copy it down, it doesn't seem to recognise my odd numbers request. What formula should I use? Thanks! Use =INDIRECT("A"&ROW(A1)*2-1) and copy down -- HTH RP (remove nothere from the email address if mailing direct) "Georgyneedshelp" <Georgyneedshelp@discussions.microsoft.com> wrote in message news:464A8951-DB29-4907-AE09-AA7CE4F0C15A@microsoft.com... > Hi, > > I want to copy A1, A3, A5, A7 etc. into a seperate column, but when I try to > copy it down, it doe...

Paste a number as text
We often use the copy paste function to export some data from our software into excel for further analysis. In one colomn we have a text field which containes sometimes only numers like "23000.3000". If we copy and paste this field, the result in excel would be "23000.3". (It is interpreted as number) We tried already to add a ' before the value but the the ' is showed as normal letter. Is there any solution to paste any number as a text in Excel without programming a full Excel automation. Thank you for your help! If it's just one cell, format it as text ...

I've got several thousand loan numbers which need to be updated to a ten-digit format by adding a leading 00. That's easy enough to do, HOWEVER, some of them have already been updated with the leading 00. What formula can I use to add 00 only to those which have not already been updated? Thanks. -- alan =right(rept("0",10)&a1,10) If the values are all numeric (not text), you could apply a custom format: Select the range format|cells|Number tab|Custom category 0000000000 or use a formula like this if the cell looks numeric: =text(a1,rept("0",10)) or =tex...

Formula Syntax
Hi I am using the formula below to bring data from one sheet to another. =SUMPRODUCT(--(TEXT(Data!\$O\$3:\$O\$5017,"mmm-yy")=TEXT(\$A9,"mmm-yy")),--(Data! \$P\$3:\$P\$5017="Tricia Woodgate"),Data!\$Q\$3:\$Q\$5017) However at the end I want to return the sum of Q3+R3:Q5017+R5017. Can someone tell me how to set up the syntax please Thanks A "Alex Hammerstein" <aph@misnet.co.uk> wrote: > Hi I am using the formula below [....]. > > =SUMPRODUCT(--(TEXT(Data!\$O\$3:\$O\$5017,"mmm-yy")=TEXT(\$A9,"mmm-yy")),--(Data! > \$P\$3:\$P\$5017=&q...

Hiding/Exposing Worksheets based on a Number Entry
I have a worksheet that has data for up to 50 technicians. Each location gets this workbook and may have from 3-50 technicians. I'd like to create a macro/formula that lets each location enter the number of technicians, and then will hide the unneeded worksheets. The workbook has a summary page, (worksheet one), fifty individual technician pages (worksheets 2-51) and a setup page (worksheet 52). Data is entered on each technicians page. What I would like is for the location to entry a number in a cell or from a macro prompt that asks how many locations they have. Based on that respon...

How do I find quotes to put in a card?
In creating greeting cards, how do I find quotes in Microsoft Publisher to insert in the card. At times quotation may be from various poets. http://www.bartleby.com/ Several quotation links there. Also..remember Google is your friend. "Claudette Sinclair" <Claudette Sinclair@discussions.microsoft.com> wrote in message news:095A66C7-7672-497E-9842-E7945533658C@microsoft.com... | In creating greeting cards, how do I find quotes in Microsoft Publisher to | insert in the card. | | At times quotation may be from various poets. How about a google search for quotations? ...

Excel Array Formula: Multiple Criteria Sum IF Challenge
Currently, I have the following Excel Worksheet Invc No Code Status Charges RejCode 291 CH no pay 50 291 CH no pay 50 291 PY no pay ded 152 CH no pay 50 152 CH no pay 25 152 PY no pay dat 206 CH no pay 50 206 CH no pay 50 206 PY no pay 507 CH no pay 50 507 CH no pay 45 507 PY no pay ded 600 CH overpaid 25 600 CH overpaid 25 600 PY overpaid ded I would like to obtain the following results, Total Charges by Rejecte "no pay" invoices and the specific "no pay" invoices with rejections a displayed below: Total Charges by Rejected "No pay"...

Cell Formatting to disp. ### numbers
I am trying to format the cells so that it only allows three numbers t be displayed. To be more descriptive: We work with zip codes quite often, but, we only use the first thre digits. Someone sent us a xls file with 12000 zip codes, in one column, and need to know how to make the column show only the first three digits o all the zip codes.. there is another problem, when I convert them to a numeric value, i removes the zero in front...ex. 08245, becomes 8245, but i need to kee that zero in front. Thanks -Eri -- Message posted from http://www.ExcelForum.com Assuming your zip codes a...

where do I find the current user in crystal reports
Hi NG, topic says everything :-) Nicolas F�hrs Many Places Depending what you want to know? Active Directory, Deployment Manger, Web Cleint Top Left=20 Corner, OUTLOOK, Settings-Business Units-Users... Q >-----Original Message----- >Hi NG, > >topic says everything :-) > >Nicolas F=FChrs > > >. > ...

Q on how to find best combination of values
Greetings all, I'm trying to find a way (an algorithm) for determining this: Maximize the value of A Keep B < 10 Data: A B F1 9 4 F2 11 6 F3 10 6 F4 10 3 F1 is 'fraction 1' I can do this manually in Excel, but when I go to larger data sets it'd be a huge pain. Does anyone know of an algorithm or a way in Excel that I could easily do this? I'm thinking that it'd take some macro programming in order to do it, but first I need an algorithm. Even if I couldn't do it with Excel/macros I'd still like to know how to do it. It's been a long time since I&#...

Using formulas to modify pivot table values
Is there a way to modify the output of the data in the body of a pivot table to be included in a calculaton. Of course this can be done post pivot table creation but I would like to do it in one go. I need to divide all the counted values in the body of the pivot table by a cell value, which is different for each row of the pivot table. Help would be much appreciated. --- Chris I don't think so. Maybe you could add an extra column and do your calculation against that (and include it in the pivottable). Or copy the pivottable and convert to values and do what you want. geechr wro...

Use Find and Replace in Excel
I have the following in cells after using a formula. #INVALID_DATA. The reason is there is no data in the cell. If i use the Find and Replace to remove the unwanted data in the cells I first use the find all command. I change the Formulas to Values. I can see the unwanted data at the bottom. I then use the Replace command. At that time I try to change the Formulas to Values. There is no Values under the replace. There is a Values in the Find section. What do I do to replace #INVALID_DATA in the cells with nothing, like using ""? I do not know what #INVALID_DATA is. Th...