Scenario Testing And Stepped Table?

Jan 22, 2014

scenario testing and coming up with a simple "multiply by" that I can drag across a table.

View 5 Replies


ADVERTISEMENT

Stepped Increment For Reference

Mar 3, 2009

If I ask anyone in my office they can easily tell me what the next line should be in this list. I can't figure out how to do it in Excel

=A5
=A10
=A15
?

So, obviously the cells are referencing column A and increasing by 5. That's it, nothing fancy. I have 5,000 cells to fill and it's increasing by 13 instead of 5 so I can't just do it by hand. If I highlight the first 3 and then drag down the next cell would say A8. Not even sure how they get that.

View 9 Replies View Related

Figuring Out Which Function To Use For Scenario

May 14, 2008

Need constructing a formula and im not too sure which function to use.
Im also not sure if its even possible or im missing a required column to help make it possible.

(arrival Time)---(c1)--(c2)--(served by)---(time served)---(exit time)
-------A---------B-----C--------D----------E----------------F
1----9.00am-----0-----0--------1----------2mins-----------9.02am
2----9.01am-----1-----0--------2----------15mins----------9.16am
3----9.05am-----0-----1--------1----------2mins-----------9.07am
4----9.06am-----1-----1--------?----------xmins-----------x.xxam

Column A = Arrival time
Column B = Number of customers in Counter #1 as of arrival time
Column C = Number of customers in Counter #2 as of arrival time
Column D = to be Served By Counter:
Column E = Mins being served before exiting the counter/que
Column F = exit time
rows = customers entering a que.............

View 9 Replies View Related

Creating A Scenario Matrix

Mar 9, 2007

Creating a Scenario Matrix. I am using Excel XP. Simplifying the problem, I have a formula;

Cell A1 has X,
Cell A2 has Y
Cell A3 has Z

X * Y = Z

I want to find the answers when X is between “1” and “5” and Y is between “1” and “5”. I want this to be in a matrix so I want the answers:

1 2 3 4 5
1
2
3
4
5

I cannot use Scenario Manager; it would record the answers on the pivot table or summary which is what I want BUT I would have to type every combination (1,1), (1,2) etc!

View 7 Replies View Related

Calculating Charges Based On Stepped Rates?

Jun 20, 2014

Never tried complicated formulas in Access and at a bit of a loss... What I am trying to do is calculate a utility bill based on stepped rated.

For example:

Usage up to the first 500KHW is billed at .067 per KWH
Usage after the first 500KWH from 501 to 999 is billed at .044 per KWH
Usage from 1000 up is billed at .0318

So if my usage was 1200 KWH...

((500 x .067)+(500 x .044)+(200 x .0318)) = 61.86

I was assuming it would require an complex "if" function to split the 1200 into steps and then calculate charges per step?

View 9 Replies View Related

Determine Cost For Stepped Pricing In Seconds

Apr 23, 2008

The phone call is charged at 0.57 for the first 60 seconds, thereafter 0.0095 per second. Determine cost if duration (in seconds) is available?

AND

The phone caal is charged at 1.654 for the first 60 seconds, thereafter 0.827 for every 30 seconds.Determine cost if duration (in seconds) is available?

View 7 Replies View Related

Calculate Total Using Base Rate And Then Stepped Interval

Feb 4, 2014

I have a base rate in A1, the the units in B1 and need a total in C1.

In A3 i have discount rate (%) for units between 0 and 9
In B3 the have the discount rate (%) for units from 9 to 15
In C3 the have the discount rate (%) for units from 9 to 15
In D3 the have the discount rate (%) for units above 16.

How would the formula looklike?

View 3 Replies View Related

Calculating Turn Around Time In Multiple Scenario

May 4, 2007

1: Clock shoud tick monday to friday 8:00am to 4:pm only
2: there are 4 coloums where date recieved, date sent, time recieved, time sent are available.
3: the TAT should show the number of hours taken to from the time a case has been recieved to the time it has been sent

View 9 Replies View Related

Shortening My Code For Multiple Case Scenario

Jul 3, 2007

I have a combo box that is used very simply to select a column from sheet and copy that whole column down to where text ends into a specific column in another sheet. I.e whatever column is selected ends up in column B of this other sheet. At the moment I've been using If functions for each case but there must be a more efficient way of doing this; here is just a sample of my

If textseries1.Text = "Fund01" Then
With Sheets("data")
Range("C1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
End With
Sheets("calculations").Range("B1").PasteSpecial
End If

If textseries1.Text = "Fund02" Then
With Sheets("data")
Range("D1:D" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
End With
Sheets("calculations").Range("B1").PasteSpecial
End If

If textseries1.Text = "Fund03" Then
With Sheets("data")
Range("E1:E" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
End With
Sheets("calculations").Range("B1").PasteSpecial
End If

There are about 75 columns in total but thought it would be a little repetitive to put it all up here!

View 2 Replies View Related

Solver & Scenario: Make A Comparison Of Two Different Staff To Customer Ratios

May 22, 2006

This is what I’ve been trying to do. There are 3 different work schedules for each employee. Each employee gets 2 days off in a row. At the top of the employee schedule worksheet, there are 3 schedules. The zeroes represents days off for that schedule and the 1’s represents days worked. Ok, Cells c7 thru c17 display total staff needed for that day Cells c8 thru c18 display average number of customer on a particular day Cells c9- 19 display number of needed based on a staff to customer ratio of 1 employee per 80 customers B11 displays that ratio

I need to make a comparison of two different staff to customer ratios. My goal is to find lowest total payroll while adequately staffing the store. In B13 I want to enter the daily salary amount for 1 employee at $10 hr/8 hrs per day

In A16 enter total payroll amount
Total staff B7 times salary in B13

Use solver to find minimum payroll amount
Target cell is total payroll found in A16
Target cell must be = to minimum value................

View 3 Replies View Related

Testing A Selection

Nov 2, 2008

I've currently written the following code to make sure exactly 2 single cell selections are made. But now, I want to extend this code to make sure only cells from rows 5 & 7 are selected and the cells are in the same column.

View 2 Replies View Related

Testing For Duplicate Entry

Oct 18, 2013

I have a form that information is entered in. At the end the user can then save the information in a spreadsheet by pressing a command button. The updating to the spreadsheet works, it is the part where the last entry is tested against the info on the form to prevent a duplicate entry that do not work.

I have tested that the program reads the info from the spreadsheet (see the commented lines).

I can attach the whole spreadsheet, but some of the entries on the form are linked to another spreadsheet that make it a bit cumbersome to attach two files.

VB:
Private Sub cmdUpdate_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("TonnageInput")

[Code] .....

View 2 Replies View Related

Testing For Blank Cells

Oct 1, 2009

I am trying to write an Excel formula to test if there is data in pairs of cells and if there is I want to copy the entire record to another Excel spreadsheet. Example: if there is data in row 1, column A and data in row 1, Column C or row 1 column D, then copy this record to another spreadsheet; if there is data in row 1 column B and data in column C or column, then copy this record to another spreadsheet. Since there is no data in row 4, column C or column D, this row would not be copied. Row 1, row 2, row 3, and row 7 would be copied to another spreadsheet. The data can be numeric or alpha numeric.

I tried using the ISBLANK statement and can't seem to get this to work properly.

View 14 Replies View Related

Testing Multiple Variables At Once

Feb 18, 2010

This code is functional but very redundant and I figured that has to be an easier way to go about this.

View 5 Replies View Related

Testing If Drive Exists?

Jun 30, 2013

I have a program that relies on a remote drive being open and ready. My current tactic (which works perfectly well) is to have a file in the destination drive and ask the date that file was created. If an error occurs I know the destination drive is not ready.

Code:
On Error Resume Next
temp = FileDateTime(destDrive & "xDeletedStuff" & "testFile.txt")
If Err Then
MsgBox "Destination Drive / Folder Not Ready"
Exit Sub
End If
On Error GoTo 0

Idealy I would rather not use the "testFile" I would prefer to just test to see if the folder existed, "destDrive & "xDeletedStuff"

View 1 Replies View Related

Testing For Blank Cell In VBA

Jan 29, 2008

I working on a loop that would find blank cell. These cells have a formula in excel, but if certain conditions are met, the cell is left blank. What property in VBA checks for blank cell? I don't thin it's the "empty" property?

View 9 Replies View Related

Testing Date For The Week Day

Oct 21, 2006

I have to test if todays day is Saturday. Don't know the syntax.

View 3 Replies View Related

Hypothesis Testing On Data

Nov 4, 2006

Performing hypothesis testing on data comprising of hours devoted to patient care per week for practitioners in conventional and alternative medical practices. I have used data analysis -> z-test-two sample for means to perform my calculations.
I would like to know if this is the correct way to perform the hypothesis calculation. See attachment for more details.

View 2 Replies View Related

Testing For First Letter Of Entry...

Feb 24, 2007

Testing For First Letter Of Entry. within an if statement, i'm testing for the first letter of another cell with the function: left(e273,1)= "D". this works well, but i only have a few letters to test for and i'd like to test for not "D", etc. (i.,e, what if the first letter is not D, then do...)...?

View 2 Replies View Related

Testing Caps Lock Status

Jun 9, 2009

I have the following codes to test Caps Lock whether it is on or off. Instead of showing a msgbox when the Caps Lock is on, I there another way to show comment or other object as well as windows UserForms

View 3 Replies View Related

Statistical Significance Testing With Excel?

Jul 12, 2012

I have simple data points based on a question that how many people liked product A between 2011 and 2012.

It looks like below

2011 2012
# of Respondents 319 329
% of respondents liking the product 78% 84.5%

The question is: is the increase from 78% to 84.5% statistically significant at either 95% or 99% confidence level. Is it possible to do this basic analysis using Excel?

View 1 Replies View Related

Testing Range For Cells That Do Not End With Certain Characters

Jul 26, 2012

Basically, looking at the range E6:L19 I need to ensure that any data that is entered into any of these cells ends in either H or W, and if it doesn't flags up a warning message (which I am hoping to link in with the Worksheet_SelectionChange event)

I'm managing it for one cell, but not a whole range.

View 4 Replies View Related

Testing Whether Subtotal Method Is Engaged

Jun 11, 2008

In a table I engaged the Subtotal (Sum) feature.

Then I did the Data, Subtotals.. Remove All

Jumping to the immediate window to enter
activesheet.select
? ActiveWindow.DisplayOutline
True

View 9 Replies View Related

Testing A String In A Select Case

Sep 21, 2009

Excel 2007 on XP, must be compatable with '03

I have a macro that sorts sets of data that is imported from a LIMS database (SQL). The macro then sorts the data based on a characteristic of the dataset that was pulled in.

I did have the macro working, however now I must add in some AI that groups together certain datasets that don't share the same parameter, as well as discard incomplete datasets, and group together unknown datasets.

In the code following the trouble section, I isolate the required tag, placing it in labelDecoded(2). Now I must test this variable against a couple of cases to see if it is one of the exceptions to the normal rule (which is to Place it in the worksheet named after what is in labelDecoded(2). A switch statement is nessasary as this is still in a prototype/proof of concept phase, and the sorting AI will be expanded in the finished product (As in more cases will be added).

Select Case labelDecoded(2)
Case Mid(labelDecoded(2), 1, 1) = "G"
getSheetName = Gas
Case "DPLS" Or Mid(labelDecoded(2), 1, 2) = "UD"
getSheetName = "Other"
Case Else
getSheetName = labelDecoded(2)
End Select

I have highlighted the sections that is giving me an error in red. The first section "Mid(labelDecoded(2), 1, 1) = "G"" Fails to evaluate to true. The Second section "Mid(labelDecoded(2), 1, 2) = "UD"" Throws a type mismatch error, that causes the macro to execute some error handling code, which by co-incidence, is the same as that case evaluating to true.

When a switch statement is called is there an object or variable that holds the information that is being processed by the switch statement? I recognize that my problem is most likely that I am calling the variable that I have allready tested to be tested again.

View 9 Replies View Related

Reagent Consumption For Blood Sugar Testing?

Apr 11, 2014

I want to solve the problem of giving an alert for opening a new reagent vial for blood sugar testing

View 4 Replies View Related

Testing For Values That Vary Within Specific Tolerance?

Apr 22, 2014

I'm trying to identify where a set of values 'agree' within a specified tolerance.

I have a list of scores from different sources, and wish to both average them (easy to do) but also to identify how well they 'agree' by flagging those which have a difference above a specified tolerance. The numbers are all integers, and the tolerance will be an integer.

To give an example, I might have cells with values (2,3,2,2) and these agree within a tolerance of 1. If I had cells of (2,4,2,2) these wouldn't agree. I'd like to flag (by the creation of an 'X' within a separate column) where these cells do not agree.

I've done this previously by using chained If and Or statements (e.g. if A1 = D1 or A1 = D1+1 etc etc) but this is laborious and means I have to change logic every time I add a new sample.

Is there a way of specifying a range of cells (e.g. A1,D1,H1, J1) and seeing if the values of these cells agree to within a stated tolerance? I'm happy to define named cells to specify and argument/attribute.

View 2 Replies View Related

Linear Regression Array - Significance Of R Testing

May 5, 2014

I have set up a linear regression array in Excel and now want to test the significance of my r2 value at a certain level of significance. I've only been able to find tables that give the critical r value, but I want to test it at 99.73% level of confidence and none of them contain that specific value. Is there a way to do this in Excel?

View 3 Replies View Related

Testing For Multiple Text Conditions Within Rows

May 13, 2009

I have an excel template that is used for communicating in projects to teams their responsibilities and defining which stream/group has Accountability/Responsibility/Consulted/Informed (known as a RACI model) for the activity.

For any given project activity, there are rules:

One person is responsible for performing the activity (R)
Only one person can be accountable (A)
Many people can be consulted through the activity (C)
Many people can be informed of progress (I)

You must have an A and and an R for each activity
And one person can be accountable AND responsible A/R

Given that project activities can run to 100 + activities in some of our more complex programmes.... I'm looking to provide a flag in the spreadsheet for when an error has occurred and a gap in Responsibility or Accountability is created. (Less worried about testing for C's and I's)

See attached, I hope it is clear.

View 7 Replies View Related

Excel 2010 :: Testing Userform - Divide By Zero Error

Mar 26, 2013

I'm the final stages of testing a userform that, in response to a button click, copies certain cells from a big messy worksheet and pastes the relevant ones (based on user input) in a clean sheet. Suddenly, I started getting a 'divide by zero' error for the following line:

VB : UpCount = PickNum - 6 + ((PickNum / 12))

UpCount and PickNum are both declared as Double, though this shouldn't matter. UpCount is being assigned a value here for the first time, and PickNum varies from 1 to about 250 depending on input.

Obviously I'm only dividing by a constant here, which is VISIBLY not zero. This error only occurs for certain ranges of PickNum...something like 50-70. Interestingly, in trying to debug it, I added:

VB:
Msgbox(PickNum)
Msgbox(54/12)

...since PickNum was 54 as I was getting this error. Just dividing 54 by 12 ALSO got a div by zero error.

Perhaps I should mention I'm using VBA in Excel 2010 for Mac.

View 1 Replies View Related

Excel 2010 :: Loop Through Row Of Data Testing Values

Dec 25, 2011

I am running excel 2010. I have set up a variable " testvalue" type variant

I would like the prog to step through a row of data which can contain text or numbers. If any number is greater than 3,000,000 I want exit the do. However, if no number is greater than 3,000,000 I want to record this as a "bad file". I have the follow code which trips at the first "case".

Code:
testcol = 1
testrow = myTextRows - 1 'penultimate row
Do While Len(wbText.Sheets(1).Cells(testrow, testcol)) > 0
testvalue = wbText.Sheets(1).Cells(testrow, testcol).Value
Select Case testvalue

[Code] ........

View 3 Replies View Related







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