Account For Two Variables - Automating Percentages Calculations

Apr 25, 2014

What I do at my job is schedule production runs for bakery items. these items consist of many ingredients that go into them (Flour, Sugar, etc.) The products go into the run at certain percentages (20% of the run is flour, 30% is eggs, 15% is Sugar, etc) Many times we will have two or more types of a product, sugar for example. between the different types they can all have a different cost per pound, so what we try to do is use the cheapest stuff first to save money. So for part of this excel sheet what I would like it to do is to automate what percentage I should use the sugar based on cost. the total percentage for the sugar would add up to 100%.

for the second half of this sheet, there is another variable to account for. for this product we also need to hit target specifications so the product will be ok to sell. The example is Protein in this situation. we have a target protein that we need to hit per serving of 24.2 grams. I have the file set up to convert the protein percentage to protein per serving in grams, so that is already done.

my problem with this sheet is I can only seem to do one or the other. I can set it up to use the cheapest product first and add up to 100%, but it won't hit the target protein. Or I can have it set up to hit the target protein, but then I won't hit the 100%, it's be above or below. I need it to account for both variables and then give me the suggested percentages.

So is there a way that I can have it formulate what percentage to use something at to hit a target and equal 100%?

tl;dr I need to account for two variables. hit target protein, and have the total percentage equal 100%

View 1 Replies


ADVERTISEMENT

Automating Data Import And Calculations

Mar 31, 2009

I have a huge database of textual files all of which have the same format - space separated text files. I need to find the average of the last column of all these files.

Each file is named uniquely. Is there an stress-free way to set up an automated procedure to go through each of these files automatically and to record the average of the last column along with the name of the file. I attached a few text files - these are daily prices of a few securities. The last column is Volume. I need to find and record the average of this column for each of these files.

The text files are here:
[url]

View 9 Replies View Related

Simplify Calculations With Multiple Variables

May 29, 2014

How to streamline calculations that include multiple variables.

I am trying to calculate results against weighted objectives for multiple associates. Here is a very trimmed down version of the table I have currently:

Associate
Role
Function 1 Count
Function 1 Time
Function 1 Learning Curve
Function 2 Count
Function 2 Time
Function 2 Learning Curve
Function 3 Count
Function 3 Time
Function 3 Learning Curve

[Code] .....

There is an acceptable range (floor & ceiling) of performance for each Role/Function/LearningCurve. In the example above, let's say those ranges are as follows:

Rookie/Function1/A = 1-2
Rookie/Function2/C = 4-6
Rookie/Function3/X = 10-15

I currently run a calculation for that associate determining what % of total time was spent on each function. I multiply that by the floor & ceiling of each range, summing the floors & ceilings to produce a weighted range. Then, I compare their production total to that range. I have all this figured out. What I'm running into, though, is that this table is duplicated on a separate worksheet for each month of the year. Beyond that, each member of management maintains their unique workbook listing this information for their associates only. What I would like to do is publish/maintain only one "master" workbook for all associates, regardless of manager.

I tried creating a master table that inserted columns for Month & Manager in front of the columns above. I tried to use a PivotTable (brand new to that process), but I think that because I have certain variables (i.e., Role & LearningCurve) that are "stuck in the midst" of my data, I can't make heads or tails of the PT.

Currently, I have 3 columns per function (Count, Time & LC) as only one LC would exist in a given month for a given function. I figure I can set up the master sheet to have 8 columns per function (account for Count & Time per each of the 4 LC's possible), but that begins to become more cumbersome than what I have already.

In the end, I want to have a table that I can filter by month and/or manager and/or associate to which I can then apply the appropriate calculations. This would allow calculation for a single month or range of months as well as being able to calculate results for an associate regardless of potential reassignment to a different manager, and conversely being able to aggregate results for a given manager using data for whichever associates reported to them each month.

View 1 Replies View Related

Complex If Statement To Calculate Date Range From Create Account To Stop Account

Sep 7, 2012

Creating a formula that basically has a big IF statement. I hope this makes sense, so here goes. I am looking to find the "age" or "elapsed days" of an ID from when they created an account to when they stopped. This might be very complex and if there's not a way to calculate the Elapsed days (even though that's what I prefer), perhaps there is some formula that can return a value like a "1" or "True" or something that lets me know the statement is true.

original formula from post 3261273
{=IF(D2>0,A2-INDEX(A$2:D$16,MATCH(B2&"1",B$2:B$16&C$2:C$16,0),1),"")}

New formulas needed or revised from the above:

Formula 1: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Create to Suspend but NO Stopped)

Formula 2: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped) OR Column 6 (Unsuspend), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Suspend Only, No stopped or Unsuspend)"

Formula 3: The ID in column 2 does not have to be the same, but if there is a value in column 3 only (Create) and there is no value in the Suspend, Stopped, or Unsuspend columns, then place a "1" in the cell. My manual calc is the "Create Only" column.

Event Date
ID
CREATE
Suspend
STOPPED
Unsuspend
Elapsed Days (Create to Suspend but NO Stopped)
Elapsed Days (Suspend Only, No stopped or Unsuspend)

[Code] .........

View 1 Replies View Related

Mapping Account . How To Add Additional Data In To Account#

Aug 21, 2009

I have two sheets. First contains Department # 0002549, and the other one contains 5 more digits(#02001 0002549). I will need to find the exact match for the first sheet to contain all 12 digits. THey are all in different order, so I probably have to match them first then add whatever the first 5 digits number. How should I tackle this issue? I was thinking to use vlookup but how could you use vlook up if only last 7 digit matches and add addtional digits to exisiting number?

View 9 Replies View Related

Scale Of Percentages Using IF Statements And Percentages?

Jun 27, 2014

I need a formula to create the following:

In Cell F5 is 26, so the percentage should be 101% (100+1). How can I modify the below to add 1% when the number goes over 100%?

Do I have to create two columns? one for the below and one if the F6 is over 100%?

IF(F6="N/A","N/A",IF(F6>=25,1,IF(F6>=19,0.8,IF(F6>=13,0.6,IF(F6>=7,0.4,IF(F6>=1,0.2,IF(F6

View 5 Replies View Related

Unzip Code - Works Without Variables, Breaks With Variables...

Feb 5, 2009

Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

View 2 Replies View Related

Automating Brackets

Mar 18, 2004

I found a template at microsoftofficeonline.com it's just a bracket. I feel it has some underlying functionality, as they have a template in visio as well, but I can't figure it out.

So, what I'd like to do is have a workbook with say 100 worksheets. Each worksheet will have a person's bracket. Each person will write in all their picks. Their will be a master bracket where the actual winners will go, as the results are posted. When the master bracket is updated, Excel will flip through each worksheet, checking to see if the win in the master bracket matches the individual bracket. Points will be awarded or not.

Can that be done? I've also done a google search and can't get anymore help. I know that the web has multitudes of free versions, which I'd use, the only problem is that people would hesitate to register. doing it in excel, on the office system, there's no need to register, your cpu login takes care of authentication.

View 9 Replies View Related

Automating Graphs

Jun 30, 2006

I'm trying to make alot of graphs(400) and I'm not really sure where to start but here with what I've got so far.

Sub macro2()
Dim x As Long, t As Long, y As Long, z As Long
y = 3
z = 5
x = 4
While x < 1000
With Worksheets("cabernet (2)")
t = Cells(x, 1).Value
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("cabernet (2)").Range("By:Hz"), PlotBy:=xlRows
ActiveChart.Location where:=xlLocationAsObject, Name:="Cabernet (2)"
ActiveWindow.Visible = False
ActiveChart.HasTitle = True
ActiveChart.charttitle.Text = " " & t
Windows("complete Favorite Genes.xls").Activate
y = y + 3
z = z + 3
x = x + 4
End With
Wend
End Sub

I don't really know if putting a variable directly into a range works, so far it doesn't.

As you can tell I need a line graph of the range B3:H5, then the next is the those same columns but 3 rows down, so the next graph is at B6:H8. I'd also like to through in a title here, but I may just be playing with fire. The title is located in column A starting at 4 and corrosponds with each graph, so it too goes is increments of 3, i.e., the first title is at 4 the second at 7 the third at 10. Somebody help me out this isn't working very well at all.

View 5 Replies View Related

Automating To Export

Sep 16, 2006

Private Sub Database_Click()
Application. ScreenUpdating = False
masterfile = "S:OfficeMaster File.xls"
Answer = MsgBox("Do You want to export to Final Database?", Buttons:=vbYesNoCancel)
If Answer = vbYes Then
ThisWorkbook.Worksheets(2). Range("q9").Copy
masterfile.Worksheets(1).Range("a4").Paste
ThisWorkbook.Worksheets(2).Range("q9").Copy
masterfile.Worksheets(1).Range("d4").Paste
ThisWorkbook.Worksheets(2).Range("b3").Copy
masterfile.Worksheets(1).Range("b4").Paste
ThisWorkbook.Worksheets(2).Range("b9").Copy
masterfile.Worksheets(1).Range("c4").Paste
ThisWorkbook.Worksheets(2).Range("e9").Copy
masterfile.Worksheets(1).Range("e4").Paste
ThisWorkbook.Worksheets(2).Range("g9").Copy
masterfile.Worksheets(1).Range("f4").Paste
ThisWorkbook.Worksheets(2).Range("i9").Copy
masterfile.Worksheets(1).Range("g4").Paste
End If
End Sub

for some reason, it doesnt work as it said the masterfile.worksheets is not recognized, but as i go to the immediate thingee, i type in ?masterfile, it give me the exact address, i had been working on this thign for hours, and have no idea what i did wrong.

View 3 Replies View Related

Automating Xirr

May 7, 2007

I have a very peculiar problem. Each month I have a set of financial products for which I calculate the IRR values using XIRR, as the cash flows are uneven. Imagine this, Product names in ColumnA, the cashflow dates in B, and the cash flows with transaction types in ColumnC and finall D has amounts in negetive and positive. Each month I add new cashflows for each product as rows.

Manually I scroll down to select the range for XIRR (both date and amount)
Offlate this work has gone really high in terms of values, and manually cacluating XIRR takes hell lot of time for all the products (currently 80 odd)

View 9 Replies View Related

Automating Data Processing

Jul 25, 2008

I have a large number of sheets with data in the following general format:

x ..... y ............ z etc

A Unique value Unique value
A Unique value Unique value
B Unique value Unique value
B Unique value Unique value
C Unique value Unique value
C Unique value Unique value
D Unique value Unique value
D Unique value Unique value
E Unique value Unique value
E Unique value Unique value

In seperate sheets, I want the following:

x y1 y2
A
B
C
D
E

x z1 z2
A
B
C
D
E

And so on for a number of columns - I always want column "x" with another column, however I want the two values for the same value of x to be next to each other, not above one another.

View 11 Replies View Related

Automating The Removal Of Certain Data

May 6, 2009

Just wondering if any one can help me come up with some code to remove some lap time in my document ....

View 14 Replies View Related

Automating Weekly Formula Changes

Dec 11, 2009

The attached workbook is a follow up report that gets run every Friday. There are 1092 cells that contain formulas that reference a file that was created on Wednesday of the same week. eg.

View 5 Replies View Related

Automating File Name Via Inputbox

Jun 11, 2007

I am trying to do is have the code display an input box asking the user to type in the file name, or generate this automatically? to an existing folder in a drive

My master file, imports data from another source (excel file) pastes it in the necessary columns at the moment, however the file is run every Monday so the file name is generic, last week was 10723, this week will become 10724 when the data is run next week,

Can I do what I want to? Via either an inputbox for the file name, or by using something so the file name becomes “107 “ & Weeknum(today()-3)

View 9 Replies View Related

Automating % Weight Calculation

Mar 16, 2008

I have 1 spreadsheet in which I need to calculate the % weight of some numbers. Columns A to G have data I do not need for the calculation.

Column H has the numbers of whose % weight I need to calculate. The problem is that the no. of rows containing the numbers can change from week to week and I am trying to automate the calculation of the % weights.

So I want try to use column G (as a reference) (like go at the bottom of it so I can know how many rows of numbers there are) and then calculate the % weights.

View 9 Replies View Related

Automating With Internet Explorer

Apr 30, 2008

I am trying to use Excel VBA to conduct a transaction automatically.So far,I managed to get all the required input in.Now,I need to click the "Submit" Button.

I was thinking of using the InternetExlorer.Document.Links(x).Title to search for the link of the "Submit" button and then use the .Navigate method to proceed to the link.

But when I wrote and ran the :

InternetExlorer.Document.Links(x).Title

i get the following mesage:

"Run Time Error 98

A property or method cannot include a reference to a private object ,either as an argument or as a return value "

View 9 Replies View Related

Automating Copy And Paste ...

May 16, 2008

I have a spreadsheet which i work on everyday, basically I have a formula which needs to be copied in the second column depending on the range in the first column.

Ex : one worksheet will have A1:A100, the B column should be copied to B1:B100, if the next one has A1:A200 then B column should be copied to B1:B200, how to do this with VBA?

View 9 Replies View Related

Automating And Looping A Web Query

Mar 4, 2009

My spreadsheet is linked to a stock data web page using the little documented .iqy function. I have set cell A1 to the web page ticker symbol, so that whenever this cell value is changed the data in the spreadsheet is automatically updated.

My problem is in several parts:
1. Read TickerSymbol.csv file which is comprised of just ticker symbols,
2. Insert (TickerSymbol.csv) symbol 1 into cell A1,
3. Save the results to a file c:XXXSymbol1.csv,
4. Insert (TickerSymbol.csv) symbol 2,
5. repeat, repeat

View 9 Replies View Related

Automating Fill Down Of Formula

Oct 20, 2006

I have a report which has several tabs of data input which is automated.

There are then several columns of formula which I manually fill down at the minute, is they an easy piece of code to automate this?

The attached example may help, on the tab data source I want to automate the copying down of the formulas in columns D&E.

Can this to be automated by the button on the tab front page?

View 3 Replies View Related

Automating Adding Worksheets

Jan 23, 2007

I have created a workbook consisting of a 'master sheet' (this sheet is a worksheet in itself but also summarizes the totals from the subsequent worksheet) and subsequent work sheets. I wish to be able to add worksheets as needed while having the 'master sheet' still updating as you add sheets.

View 9 Replies View Related

Account Reconcilition

May 21, 2006

to write a macro to put the background color where a certain figure sum is zero. Eg. Column A contain as
5
-5.25
12
-5
-12

If one select A1 to A5 and run the macro the color for all cell should be green except for A2. Infact the macro will point out which amount is not deposited into the bank. (All +tives are for Deposits and all –tives are for sales)

View 9 Replies View Related

Automating Saving Tabs Used For Billing?

Sep 15, 2012

I've been experimenting with automating saving tabs that we use for billing into a .prn file. It works great on my workstation but when I run it on a coworkers workstation the file gets output to the Windows 7 Documents Library, not the workbook's file path. I want it to Save in the same file path that the workbook is saved in already.

Just to clarify I have many sheet tabs, and these are all saved in the .xlsx file. I was previously clicking one by one and doing save as, other file type, prn... but I've automated that by recording a macro and hacking away at it. specifying the workbooks file path correctly? Here's my code, truncated to just show the name of 2 of the tabs:

VB:
Sub EXPORT_ALL()
'
' EXPORT_ALL Macro
' This Macro saves the sheet then exports all tabs as a PRN.

[Code]...

View 2 Replies View Related

Automating Count Of Various Text Values

Mar 11, 2014

I have 300 cells filled with around a dozen different names - i.e. the same names crop up multiple times. What I need is automatically creating a list of all the names that appear, ordered by how many times they appear and with the number of times they appear next to them - i.e. ranking the names by their frequency.

View 3 Replies View Related

Automating Outlook Calendar Invitations

Apr 7, 2014

How to automate outlook 2010 calendar and email integration.

I would like to create a button that sends out calendar invites to a large number of recipients with different specific meeting details (date determined by spreadsheet and other details standard). I'd expect that this would populate a specific shared calendar (not my personal calendar) for managing these appointments. It would be good to avoid duplicate correspondence (e.g. via drop-down list or checkbox).

I've created buttons testing a few codes provided in forums, but have not been able to make these work correctly.

Attached is a simple excerpt of the spreadsheet I'm seeking to automate.

Test example.xlsm‎

View 1 Replies View Related

Data Validation - Automating Process

Feb 12, 2014

I'm currently trying to automate the process of creating Data Validation.

PFA.

I have Grades and Names List.

" GradesList " Range has values "One, Two, Three, Four "

I'm trying to map Names List to GradesList using INDIRECT().

Debug the function DataValidation() to understand the issue better.

View 7 Replies View Related

Automating Repeated Copy/paste

Nov 23, 2009

I've data in a sheet (case1) which I would like to paste in a new sheet (case2) in the shown format. Is there any easy way of automating this? I tried recording a macro & tried somehow to automate this task but gave up after a few tries?

View 8 Replies View Related

Automating Multiple Cells Using A Stat

Sep 24, 2008

I have a row with 5 numbers, B1:F1.
A1 is my muliplier cell (percentage).

I want all of the above to be variables (no formula in these cells) and controlled by a different cell (G1).

What function would I insert into G1 to be able to control the figures in B1:F1 via A1.

View 9 Replies View Related

Automating Work Task Using Validation?

May 25, 2012

i have a validation list in A5 pointing to F6:F, i need the refmask_no to change according to the validation list in A5:A.

So if i choose from List LEADCASH the refmask_no= '100007995' etc

How can i automate the changing of refmask_no?

Sheet1  ABCDEF4Validation ListCorr _acc_no  Mapping 5LEAD CASH ABNAMROrefmask_no = '100007997'  
Corr_acc_noLead Set6    '100007995'LEAD CASH7    '100020741'LEAD CASH ABNAMRO8    '100020645'LEAD CASH AUDNZNR9 

View 1 Replies View Related

Automating Copy And Pasting Process?

Oct 24, 2012

I was wondering if there was anyway to automate this process. Say take all the files in this folder copy visible cells on a specific tab and paste them into a new worksheet/model we already have built. They shouldn't be consolidated rather pasted below the next.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved