Convert Time On Scale Of 100 To Scale Of 60?

Feb 1, 2013

How can we convert time on a scale of 100 to a scale of 60?

View 3 Replies


Calculation Time Not Scale With Worksheet Size

Feb 14, 2013

I have a rather large spreadsheet that takes a long time to calculate. One sheet has the data (about 2800 rows by 650 columns), with samples in rows and data for each sample in the columns. A second sheet has functions to group the data according to predefined patterns. That second spreadsheet is about 800 columns wide and as many rows as I need it to be, as the samples don't need to be analyzed all at once.

Ideally I would like to do all samples at once by making the second spreadsheet 2800 rows high, but the calculation time is just way too long. And there's the problem. The calculation time seems to increase exponentially with the number of rows I calculate at a time. I ran a few tests making it different sizes, and here are the results:




I tried both HLOOKUP and INDEX/MATCH functions and it didn't make a lot of difference. These numbers are for INDEX/MATCH.

Graphing these numbers show that it follows an exponential curve pretty closely. What is interesting is that the progress indicator at the bottom of the screen finishes in a few seconds, about 5 seconds for 350 rows, and I can see the new data after those few seconds. But then it sits and appears to do nothing for the rest of the time. So, for 350 rows, it looks to be done with the calculations within 5 seconds, but then it freezes for another 2 minutes with CPU usage maxed out before I can do anything with the program. That actual calculation time seems to scale linearly with the number of rows I calculate, but the time after it finishes calculating before it finishes whatever else it's doing scales exponentially.

The spreadsheet does have a macro and a couple of other sheets and a macro that do some later analysis and I wondered if that had something to do with it. To check that, I created a new spreadsheet and set it up the same as the original one, with the data in one spreadsheet and the INDEX/MATCH formulas in the other. I copied the data over, but setup the formulas new without copying/pasting anything. It didn't make any real difference.

View 2 Replies View Related

Counting Number Of Days Over A Time Scale - Graph

Oct 6, 2012

I have got a project that has several hundred starts and finishes. I want to count them over time to use as an indication of progress. ( I want to show this graphically)

I have got the graph working but it is pretty long handed.

I have tried using a pviot table and picvot graph which almost does it but has a few problems (if there is no data in a month then it wont create an entry for the month, and the cumulative over time is not quite working).

Both examples are in the attached file : Progress Curve ozgrid1.xlsx

View 6 Replies View Related

Excel 2007 :: Inconsistent Time Scale - X Axis

Mar 8, 2012

Basically I have some performance figures for a fund that I want to make a chart for, the problem is that the first date (the start date of the fund) is 15/09/2011 but every reporting date after that is the end of the month.

So my data looks something like this (not real figures)

15/09/11 20
30/09/11 22
31/10/11 23
30/11/11 25
31/12/11 26
31/01/12 27.5
29/02/12 29

So what I want is a graph where the x axis starts at 15/09/11 but then the next label is 30/09/11 and 31/10/11 etc, while still keeping the scale proportionate to the dates, ie the distance between 15/09/11 and 30/09/11 will be half the distance between 30/09/11 and 31/10/11.

The 2 outcomes I seem to be stuck with is either forcing the graph to start at 31/08/11 and making the y axis cross at 15/09/11, however with this the x axis goes left past the y axis cross point and it just looks dumb, although everything right of the y axis is correct.

Or secondly I get the graph to start at 15/09/11 and set it to label every 1 month and all the labels are 15/09/11, 15/10/11, 15/11/11 etc

I have access to excel 2003 or 2007.

View 4 Replies View Related

Add Vertical Line To 2 Graphs With Scale X Axis At Same Time

Sep 16, 2012

Two graphs with different scale x-axis.

Graph No. 1 with smaller time frame, Daily.
Graph No. 2 with larger time frame, Weekly.
Objective: Add vertical line to mark the same times in both graphs.

Trouble is both graph already utilise the secondary axis. VBA is allowed.

View 6 Replies View Related

Scale In X Axis

Apr 12, 2007

The scale on our chart in the X axis is mandating a numeric number. It is showing "39200" instead of 4/28/07. In any event, we would like to have 4/9/07 -- the last date we have data for and the last date that we have referenced on the X axis.

The chart is in the attached chart.xls and the data is in the attached chart.xls

View 9 Replies View Related

Sliding Percentage Scale

Jun 18, 2014

A set low number of 100 is in cell B4, and a related set high percent of 18% is in cell B6.

A set high number of 1,000 is in cell D4, and a related set low percent of 14% is in cell D6.

In cell B8 a manual number will be typed between 100 and 1,000.

In cell B10 we want a formula that will roundup a percentage number between 14% and 18% depending on the number inserted in B8.

View 7 Replies View Related

Using IF Function To Create Scale

Jul 23, 2014

I am trying to create a scale using IF functions in Excel.

I have a row of numbers that I want to convert to my scale. My scale is fairly simple, less than 1 =1 and more than 10 = 1
After that, any number between 1-2 gets a 10, any number between 2-3 gets a 9, any number between 3-4 gets a 8, etc.

So, what I have done is take a logic if-then function and applied it to my row of numbers. It looks something like this:


However, this doesn't work. I have a number (2.55), and instead of showing a 9, it says FALSE. I am pretty sure 2.55 falls between 2 and 3 on the scale. Why won't it work?

Also, is there any way to create a function where I could put each of my if/then functions to create my whole scale? Right now I just have 10 separate columns for each number on my scale.

View 1 Replies View Related

Scale Differs From One Computer To Another

May 9, 2014

Here's the scenario. A co-worker and I access the same file from the same online database and download it in the same manner. We have the same version of excel. We then open these identical files, we run a formatting macro (identical), but when it comes time to set the page breaks in order to get the items to print on the same page I have to increase a columns width to make it fit on one page and yet it still doesn't match theirs. It appears to have a different scale but I'm not sure of the option difference between our machines. I don't know how it could be the printer since it physically gives her different page break option than what mine does.

View 1 Replies View Related

Scale / Resize Image With VBA?

May 1, 2014

I would like to scale an image to 124% how do I do that

View 1 Replies View Related

How To Set Auto Scale Mode To DPI

Feb 14, 2014

My display scale is 125%. A college's is set to %100. This causes errors when I place Shapes to saved locations, .Top & .Left programmatically. Based on Google searches, it appears that setting the auto scale mode to DPI may solve this problem. How do I set it?

View 5 Replies View Related

Price Movment On A Scale

Jan 26, 2009

I have a list of items that each one has starting price, demands and current price. Current price changes by demands according to pre determined scale. Demands values are entered manually by user. Iím looking for a formula that will calculate current price automatically according to the scale & demands. I cannot know what will be the amount of demands (it could be 0 or 5000...) but I do know that demands are sequential integer numbers, positive or negative. I also donít want it to exceed the minimum and maximum value that determine by user. I think the attached file will explain well than what Iím writing.

View 5 Replies View Related

Update Chart X Scale With VBA

Feb 16, 2012

I am using a VBA macro which performs a Yahoo webquery to obtain stock data. After the data are downloaded, a chart is updated with the new data.

The current macro updates the scaling on the price axis just fine, however, if I change the time frame the x axis, i.e, time/date axis is either spread out to far or jammed up.

The code for re-scaling the the y axis is shown below:

Sub UpdateScale()
Dim ChartVar As Chart
Dim lMax As Long, lMin As Long
On Error GoTo ScalingProblem
'Assigns the values in the Min and Max ranges to variables.


Any similar sub that would also rescale the x axis as needed when the data intervals are changed?

View 6 Replies View Related


Oct 3, 2008


0 - 49.99 = 9.9%
50 - 599.99 = FIRST 50 @ 9.9% (4.95) THEN AT 5.8%
600 ABOVE = FIRST 50 @ 9.9% (4.95) + 50 - 599.99@5.8 (32.45) AND THEN 600 ABOVE AT 1.9%

this is what i came up with, but it is not excepting the formulae.


View 9 Replies View Related

Z-Transformation (scale My Data)

Oct 14, 2008

My question is that i have to scale my data.. Can i do Z-transformation..
Is there any inbuilt function or shall i have to write Code,

View 9 Replies View Related

Calculate On Sliding Scale

Nov 19, 2006

Calculate the intensives to be paid to a loan officer based on the number of cases he brings , according to the following :

the first 10 cases 0
from 11 to 15 cases 6 pounds
from 16 to 20 cases 8 pounds
from 21 cases to more 10 pounds

so I need to put the total number of the cases , the loan officer brought in one cell, and this value to be processed and splitted to the segments above , and multiplied with adjacent pounds sums , and finally calculate the total of theintensive to be paid.

View 9 Replies View Related

Scroll Scale Of Graph

Dec 7, 2006

I have a big set of data, where I requires to zoom in to view the details of the graph (by scaling the X and Y axis). I am thinking of a graph that can use 4 scroll bar to control the zooming. 2 for each axis, where one control the difference between the maximum and minmum value (Zooming effect, minimum value always the same), and one to scroll the range of the data without zooming, (maximum - minimum) always the same.

View 5 Replies View Related

Grey Scale Text

May 15, 2007

We are trying to print grey scale text. It will only print grey scale when we print in colour and not in black and white?

The images and line fills we are using are printing in grey, just not the text.

We are using excel 2003. Both word and publisher print grey scale?

View 3 Replies View Related

Scale Chart Series To MIN & MAX

Sep 26, 2007

I have a Scatter chart that plots a simple XYScatter plot. The data for this plot can be very wide ranging, and for that reason, I use AutoScaling to scale the axes. Nine times out of ten, it works just fine, but it seems that when the X data ranges from about 5200 to 7200 +/-, the autoscaling does not calculate well (example attached).

Autoscale range will go from 0 to 8000, leaving a large area of unplotted graph space.

I thought the original issue was that I may be plotting zeros or blanks, but I've eliminated that problem. I have yet to find another range of values that causes it, but I'm sure there's some. What is it about the way that Excel calculates its AutoScaling that I'm missing?

What do you all recommend to eliminate this problem? Manually scaling it is not really an option in this case, as I want this to be completely transparent to the user for ANY range of data.

View 9 Replies View Related

Color Scale Conditional Formatting

Jun 11, 2014

Color Scale Conditional Formatting

I want to use formula to define Minimum, Midpoint and Maximum in the color scale, but am not able to achieve it.

My Criteria are as follows;

Minimum: Less than equals to 50%

Midpoint: More than 50% less than 70%

Maximum: More than 70%

I need this formula for Accuracy column only.

View 3 Replies View Related

Calculate A Linear Sliding Scale

Oct 9, 2008

I need a formula to calculate a linear sliding scale from 10%-30% (no higher, no lower). Based on the data in the below chart, if the GROR (let's say this is known and the result resides in cell A1) is less than or equal to 0.0% the payout % is 10.0% and gradually increases as the GROR increases.

The payout % is capped at 30% which is if GROR is 18% or greater. Below are just some examples of how certain thresholds should calculate.

GROR 0.0%5.0% 8.0% 11.0% 14.0% 18.0%+
payout %10.0%10.0%14.6%19.2%23.8%30.0%

View 2 Replies View Related

Same Color Scale Looks Different In Multiple Worksheets?

Mar 22, 2013

I have a row containing numbers from 1 to 4 and I want to use a color scale to differentiate between the cells easier. The problem is, that I have multiple sheets and in some sheets, I get a different color scale, even though I use the same command and color scale.

I've already removed the "conditional formatting" on all worksheets, but the problem isn't resolved. Is there another way to clear the formatting of a cell, besides going to the "Conditional Formatting" tab and clicking "delete rule for entire sheet"?

View 3 Replies View Related

Scale Currency In Multiple Cells

Nov 19, 2009

I have a spreed sheet with multiple cells with currency values. I would like to be able to scale all the values by given percentage if possible.

View 3 Replies View Related

Having A List Of Macros Available To Use On A Global Scale

Jan 27, 2010

I have never used or created a Macro before. I have worked out how to record one etc. What I would like to know is, is it possible to design a macro and whenever an excel workbook is opened it will list all the Macro's you have created so that you can just action on any sheet or work book.

View 8 Replies View Related

Maths / Calculate Scale Of A Set Of Numbers?

Dec 11, 2012

I have a need to work out a scaled trend 1 - 10

for example if I have the following data set:


Product A


View 4 Replies View Related

Non Linear Scale Applied To Data

Mar 8, 2008

in this example below i need to work out what the divsor would be for values inbetween the data?

View 9 Replies View Related

College Gradebook Average Scale

Oct 16, 2009

I have the following grade scale

F = 0%, D = 60%, D+ = 65%, C = 70%, C+ = 75%, B = 80%, B+ = 85%, A = 90%, A+ = 95%

and I have these students total marks


I want the student who took 84% which is the best to take A+

On other way, I want these marks to be on average instead of the standard scale

View 9 Replies View Related

Rank Values Into Top/Bottom X Scale

May 21, 2008

I am creating a Power Poll Spreadsheet for a Fantasy Football League, and I need to create a code that will take the win/loss columns and in a new column assign rankings so that the team with the most wins gets ranked '1st', then '2nd' etc. I'd like it to, in the case of a tie, rank both teams equally 'the 3rd and 4th best teams both have identical records, so both get ranked '3rd' and the '4th' ranking gets skipped.

I have searched for a formula to allow this, and I can't find it. Can anyone help? As an aside, is anyone aware of a place I can find a listing of formulas that can be used in sports ranking, etc?

View 8 Replies View Related

Scale A Number Using Format Cells Custom?

Aug 6, 2009

I have a number in a cell of 1025132.36. I need to scale it down to this number with this formatting: 1,025.13 (Two decimal places with comma separaters)

Basically it is dividing it by 1000 or scaling it by 3 (move the decimal place to the left three places). I cannot use /1000 in the cell and I cannot reference it to/from another cell. I must use something under Format/Cell. I was trying to create a custom format.

I saw in another forum thread to use #Ē.Ē###. This does the scaling but then I canít make the number viewed with only two decimal places. Also when I tried to add comma separators for thousands it put a comma right before the decimal.

View 4 Replies View Related

Spinbutton To Increase By Different Increments According To Current Value Scale

Jan 1, 2009

I don't know if title is understandable, English not being my tongue, anyway here's my problem, the current value must be increased or decreased by 0.01 if it's between 1 and 2, by 0.02 between 2 and 3 and so on. I'm really new to VBA and after a few hours I came up with the following code which works fine till the value of 6 and then gets stuck: ....

View 14 Replies View Related

Copyrights 2005-15, All rights reserved