Rounding By Varying Multiples?

Feb 27, 2014

I need to be able to round a given number (stock required) up to the next multiple of another number (pack size), as in the attached example.

Also, is there a way I can round to the closest multiple, ie either up or down, depending on which multiple is closer? I haven't decided which way I want to go yet!

View 3 Replies


ADVERTISEMENT

Add Standard Set Of New Worksheets In Varying Multiples To Workbook

Feb 16, 2012

I would like to be able to add a standard set of new worksheets in varying multiples to my workbook from time to time.

How would I:

1. COPY the entire contents of a worksheet I've named 'Last' (content may be added to from time to time - so 'copy all' rather than a set range, so that:

2. Each worksheet created after 'Last' is named in turn 'Vendor1', 'Vendor2' etc

Sometimes I will need to create, say 10 further w/s 'Vendor1' to 'Vendor10' other times maybe 50 w/s. or more.

After these new w/s are created they will be manually renamed to the name of the actual vendor - so this naming convention of Vendor1 etc, will not cause problems with existing named worksheets.

However, the position in the sequence of 'Last' will obviously change as new w/s are added so the VBA would need to take this into account.

View 1 Replies View Related

Varying Validation Drop-downs Based On Varying Cell Values

Aug 7, 2013

I am attempting to have cells in Column 'U' deliver different drop-down menus based on the corresponding value in column 'D'. I have created 7 named lists:

List_117G
List_152
List_JMET
List_XBAND
List_PACWIND
List_VORTEX
List_ROVER

Those lists will be called up based on 7 values in column “D”:
“G”
“152”
“J”
“X”
“D/E”
“V”
“R”

So far I have only been able to get this to work for the first category “G”. When I change the value of column “D” from “G” to “152” I no longer get a drop-down. Here is the formula I am using in the List function of validation.

=IF(D6="G",List_117G,IF(D6="152",List_152,IF(D6="J",List_JMET,IF(D6="X",List_XBAND,
IF(D6="D/E",List_PACWIND,IF(D6="V",List_VORTEX,IF(D6="R",List_ROVER,)))))))

View 2 Replies View Related

Combine Rows Of Varying Length And Varying Number Of Occurrences

Jun 28, 2012

There are 11,000 rows and 4,000 are unique. The goal is to merge the data down to the 4,000 records. Each of the unique entries shows up 1 to 15 times.

In trying to solve this, I wonder if I should break this down into the different # of occurances and implement specific solutions. e.g., There are

5700 entries that show up 2x
504 that show up 3x
24 that show up 12x

View 3 Replies View Related

VBA - Call Macros With Varying Names And On Varying Modules

Oct 2, 2013

I want to call a macro with a varying name that is within a module with the same name.

I have a module called Test1 and within this, a macro name called test1
I have a module called Test2 and within this, a macro name called test2
etc

On another module called Test8 (with the macro called Test8), this Test8 macro will call either Test1 or Test2 or Test3 etc depending on what I choose in an excel spreadsheet. So on sheet1, cell A1, there is a drop down with the options Test1 or Test2 or Test3 etc.

The following works to run the macro test1 from module test1 (when it does not vary i.e. i physically put in the name of the macro myself):

Sub Test3()
Test1.Test1
End Sub

The following works to run the macro test2 from module test2 (when it does not vary i.e. i physically put in the name of the macro myself):

Sub Test3()
Test2.Test2
End Sub
etc

However, if I try it so that the calling of the macro varies as below , it does not work:

Sub Test3()
MacroToCall = Sheets("Sheet1").Range("A1").Value
MacroToCall.MacroToCall
End Sub

View 7 Replies View Related

Banker's Rounding Or Scientific Rounding

Jul 9, 2008

I am trying to round similar to Banker's Rounding or Scientific Rounding but I can't find a consistent formula that works perfect with decimals.

Using three decimal places for all the samples, I can get 0.0785 to round to 0.078 but 0.1785 wants to round to 0.179 instead of staying 0.078. Or 0.0005 will round to 0 but 0.5115 wants to round to 0.511 instead of 0.512.

Here is a list of sample numbers along with desired results:
.0785 should be .078
.5115 should be .512
.5035 should be .504
.0005 should be 0
.0025 should be .002
.0194 should be .019
.0195 should be .02
.0135 should be .014
.0115 should be .012
.8115 should be .812

I cannot find a formula which gives me all of these results. Here is a list of the formulas I have tried so far (NOTE: cell A2 is the working cell in my worksheet where I enter the number to be rounded)

1) =MROUND(A2,0.001)

3) =ROUND(A2,3)

4) =IF(ISERROR(IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOOR(A2,0.001))),0,IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOO R(A2,0.001)))

5) =EVEN(A2)

6) =ROUNDUP(A2,3)

7) =ROUNDDOWN(A2,3)

View 9 Replies View Related

VLOOKUP With Multiples

Feb 4, 2014

I have attached an example. on the "master" sheet in column L I need a vlookup which will look up the information in column I & J and return the appropriate amount for that from column C of the "sheet3" tab. If there is nothing it should just return 0.

Sample Macro jscalem.xlsx

View 4 Replies View Related

Multiples Of A # Formula..

Oct 13, 2008

Is it possible to have a forumla that gives a True or False (perferably if it's possible for a conditional formatting to change colors) for multiples of a number. I would input a number in the cell and it would change the cell color if it not a multiple of say for example 13.

View 5 Replies View Related

Round Percentages (Up And Down) By Multiples Of 5%

Sep 29, 2009

How do I round percentages, both up and down, to the nearest 5%?

Ex. 82% to 80% as well as 84% to 85%

View 4 Replies View Related

Output Data In Multiples Of X?

Aug 5, 2013

I need the output in B1 to be in multiples of the value in A1, rounded up.

example:
Value in A1 is 6
random formula in B1: =IF(AND(N2>$AA$1,S2>0),ROUND((30/$AA$1)*AA2-Z2,0),IF(AND(N2>$AA$1,S2=""),ROUND((30/$AA$1)*AA2-Z2,0),IF(AND(N2<$AD$1,S2=""),ROUND((30/$AD$1)*AD2-Z2,0),IF(AND(N2<$AC$1,S2=""),ROUND((30/$AC$1)*AC2-Z2,0),IF(AND(N2<$AB$1,S2=""),ROUND((30/$AB$1)*AB2-Z2,0),IF(AND(N2=M2,N2<$AA$1,S2>0),ROUND(30*(AA2/N2)-Z2,0),IF(AND(N2<>M2,N2<$AA$1,S2>0),ROUND(AA2*(30/$AA$1)-Z2,0))))))))

So if the resulting value of my B1 formula is 2.33, I'd like to have the output be 6. If the resulting value is 14.55, I'd like the output to be 18.

View 2 Replies View Related

Delete Shiftup Multiples Of 25

Sep 17, 2009

Is ther a way to search a column from M9 down and delete all numbers that are not multiples of 25. Also delete shiftup column N with it? i.e.

From:
1200.......399
1225.......700
1250.......325
1260.......320
1274.......500
1275.......251
1300.......102
To:
1200.......399
1225.......700
1250.......325
1275.......251
1300.......102

View 9 Replies View Related

Pivot Chart Top Population Multiples

May 5, 2014

I'm trying to make a sales chart defining the top 6 most popular items I'm having a problem.

Attachment 316136

View 1 Replies View Related

Excel Return Multiples Cell Behind One?

Mar 8, 2014

I need to create a vlookup to find a cell and returns all cells that are underneath, in the example:

jicRD.jpg

I need to find the color and return all cells that are down

View 7 Replies View Related

Countif - Counting Multiples Of Same Value In Same Cell

Oct 3, 2012

I've got a head-scratcher here that no one in the office can figure out (at my level anyway). I'm trying to count multiples of the same value within the same cell. For instance, if one cell's value is:

2, 2, 4, 5

I need my '2' count column to return two '2's. I'm using a wildcard because the cells will contain the numbers 1-7, some multiple times, and usually in different orders. The wildcard formula I'm using only picks up one if multiples of the same value are entered in one cell. Here's the formula:

=COUNTIF($C$4:$AG$60, "*"&"2"&"*")+COUNTIF($C$4:$AG$60, "2")

This formula only returns a value of one in the '2' count column. It can't find the second '2' for some reason. I haven't been able to make COUNTA work either.

View 4 Replies View Related

Countifs In Multiples Sheets Calculation?

May 7, 2013

i have created a file for tracking my employee trandiness however, when i want to count the M, P, X ,L & AP keys which i already created it gives me OK for one month but when i tryied to calculate more than sheet "month" gives me error !! i used the below farmula :

=COUNTIFS(tblJanuary[@[1]:[31]],KeyPersonal)

how to calculated the above fx in every month. !!

View 5 Replies View Related

Converting Input Column Into Multiples

Jan 6, 2009

I know Excel quite well but this problem has me beaten and I have searched high and low to know avail.

I have an input column of departments and names which is ever-changing and currently I have hard-coded them into a spreadsheet to run a couple of drop down columns. However, I really want this to be automated and no longer hard-coded so that names are in the correct departments automatically.

View 9 Replies View Related

SUMPRODUCT Equation Based On Multiples

Mar 4, 2009

to create the following formula which I'm now repeating in more elaborate forms. At the moment I am using the formula below but I need to get the bit in red in the second half of the formula to search on more than the one condition.

Currently it is searching a postcode abbreviation field and is picking up all those with 'BS' however I don't just want to search on BS. I would like to search on BS, BA, SN, TA, GL. I have tried putting different combination using commas and plus signs etc in but excel doens't seem to like the way I'm doing it.

=SUMPRODUCT(--(Sheet1!$T$3:$T$10000=A49),--(Sheet1!$S$3:$S$10000="BS"))

View 9 Replies View Related

Values In Range 1, Multiples Of 10th

May 3, 2006

My question is presented, in details, in the attached file. Only Range 1 (B6:B15) should be generated by "Excel". (the numbers in Range 1 were typed by me as trials only...) (All other Ranges should not be touched). The values in Range 1 can be Multiples of 10th. only. (10, 20, 30, 40, etc....)

View 3 Replies View Related

Calculate Time Difference In Various Multiples

Oct 16, 2007

I am setting up a time and attendance system.

What I want to do is calculate the overtime that someone has worked but in multiples of 15 minutes.

Example, if someone worked 20 minutes over they would be paid for 15 minutes overtime.
If someone worked 31 minutes over they would be paid 30 minutes overtime.

The possible overtime someone could work in one day is 6 hours.

I want it to return the overtime in decimal numbers (e.g 0.25 for 15 minutes overtime).

I have attached a sample spreadsheet.

I would prefer this to be done in VBA if possible?

View 3 Replies View Related

Count Rows Between Occurrences In Multiples Columns

Dec 24, 2006

How do I create a code that record on row 1012, starting with column N, will count the number of rows between each "0" record in the range F7:F106. Perform the same function for number "0" in the range G7:G106 and recording in N1013.

Perform the same function for number "0" in the range H7:H106 and recording in N1014. Perform the same function for number "0" in the range F7:H106 and recording in N1015.

View 9 Replies View Related

Create Table Of Dimensions With Multiples Of Variables

Oct 11, 2007

I am trying to create a spreadsheet containing a table of dimensions that are multiples of up to six loadable variables. I work for a stainless steel tank manufacturer. The body of tanks are formed from coil stock of varying widths. Typically these might be 1200mm, 1500mm, 2000mm and possibly 1800mm as well as the imperial equivalents. This could mean up to eight variables but in reality all are unlikely at any one given time.

We are told the volume that the tank is required to contain and a target preferred diameter. The theoretical height is therefore derived from this information. But now comes the tricky bit. It is desirable, to minimise cost of welding by minimising the number of strakes or wrappers. Can anyone give me a formula or whatever that can be used to do this and hopefully generate a table of close heights up to say 40 meters. Thus allowing an operator to select from say five presented options.

I have a table in a spreadsheet that the three principal widths of 1200mm 1500mm and 2000mm generate. It was created for me a while ago by a gentleman working at the Amsterdam Museum that Im happy to send for perusal. The trouble is he never told me how he did it and no matter how hard I try, I cannot discover the method or formula. The first column gives the total combined height, the second the number of 1200mm wrappers, the third the number of 1500mm and the last the number of 2000mm. It is that simple

View 4 Replies View Related

Round Total Time To One Hour Multiples With A Minimum Of Two

Apr 4, 2009

I need a basic formula that will take my start time from my end time and round it up to the next hour with a two hour minimum. I formated my cell to not have decimals and I tried to use round and roundup along with and if/then formula but no luck. If it makes any difference, all times are in 24hr format.

View 6 Replies View Related

How To Use Design Mode To Create Button For Multiples Macros

Mar 26, 2014

Is it possible to use design mode to create a button for multiples macros?

What i want to do is create like a list, and every selection run a different macro. I have a report that i want to use macro to make different filter selections, it would be about 20 different filters combination, so 20 buttons would be a mess... Is that possible or can i do it in another way?

And is possible to link the filter value to a different cell?

View 2 Replies View Related

Cells To Be Able To Only Allow Multiples Of Fifty And A Message To Pop Up Of Invalid Entry For Everything Else

Apr 19, 2009

I need cells to be able to only allow multiples of fifty and a message to pop up of invalid entry for everything else.

View 9 Replies View Related

Userform Verification (avoid Multiples In A User-driven/created Database.)

Feb 15, 2010

I would like to add a piece of code into the user form that will check and verify if a part has already been added so as to avoid multiples in a user-driven/created database.

here is a repost of the current code i am using for the user form (I have posted it before in another thread .. Blane245 helped me out with a different question I had)

View 7 Replies View Related

Sum A Varying Range

Nov 17, 2006

I want to sum a range of cell in a macro. The range can change depending on what is imported. For instance, my range is from (Ax:Ay) and sums this amount on the cell below Ay.

View 9 Replies View Related

Auto Sum A Varying Range

Nov 11, 2008

I am trying to auto sum a range of cells, in 3 columns, that varies from sheet to sheet.

I have attached a sample sheet. The columns I am trying to total are columns "E", "F" and "G".

View 9 Replies View Related

Varying A Range In A Formula

May 30, 2009

I need some programming help to control the length of the range in the formula below:

View 4 Replies View Related

Varying Lengths Of Digits

Jan 16, 2006

My data is as follows, all in one cell:

10015200 US Government Bonds 18,369,423.52
10012301 US Government Bonds 10,232.21

There are many varying lengths of digits. I am trying split off the numbers
to the right of US Government Bonds into another column. Here is my
formula:
=RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7)))

My result is 9,423.52. Its not pulling the whole number.

What do I need to change in the formula?

View 14 Replies View Related

Hyperlink To Varying Web Pages

Dec 5, 2008

I have a spreadsheet that has different product codes that I want to link to their corresponding web pages.
At the moment I individually input the hyperlink into each cell using this common address http://www.ace-sales.co.uk/Catalog.aspx?search=25617 it is just the 5 digit code at the end that changes each time
Can I put a common hyperlink in all the product code cells, then just put the product code in the cell and then the hyperlink automatically links to products corresponding page?

View 8 Replies View Related







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