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


ADVERTISEMENT

How To Use INDEX Formula In Conjunction With Data Validation To Output Data

Jan 9, 2014

Essentially, I would like a user to be able to select a PUB from the Data Validation drop down in row 2 of the PUB RATES sheet so that the corresponding information in the DATA sheet autofills.

Currently, this works only for the first column under each PUB when selected and this fills across all 4 columns (rather than the respective information for each column filling).

Also, the Data Validation dropdown includes blanks which I would like to exclude.

I have used a Range Name for the Data Validation of each PUB so that these can be drawn from a separate sheet as I don't want all the DATA content on the same sheet as the PUB RATES content.

View 4 Replies View Related

Extract Unique Data From Information In Output Data?

Jul 2, 2014

I am trying to extract unique Data from the information in the output data in the file attached after i have extracted data based on criteria from the database sheet. i have noticed that because i extract data from the database sheet and from there i perform the extraction of the of the unique data , it is not able to extract unique isser name form the output sheet.

View 1 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

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 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

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

Set Data Output Range

Feb 11, 2010

I want to set my output range to start at row 25 Set Findit starts counting cells from row1.

View 2 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

Match Data Sets For Output

Aug 26, 2007

I am presently doing one of my work in excel manually. I m not expert user VBA. Yes, I do understand codes, but not very good at developing. I am attaching a file which has all the requirements in details. I think this will require hell lot of programming skill, which I dont posses.

View 7 Replies View Related

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

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 I’m 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

How To Use Buttons To Lookup Data Then Calculate Output

May 1, 2013

I am struggling to begin designing a program to use for quoting sheet metal parts. We buy three types of material in 9 different thickness. The material is purchased by weight but I sell it by square foot.

I tried a few training exercises using macros but I dont believe macros is the proper function to achieve my output.

View 6 Replies View Related

Link Graphed Data To Output Cells

May 19, 2008

I am trying to get the information that I have plotted, on a scatted graph,
off onto the second sheet an splitting the information. I'm not too sure if
this is the right place for this thread, but I am hoping that someone can
help?

The original data that is on the graph is in 3 columns. One has the company
name, one is the current value and one is the predicted value.

I need this info to come off the graph or from the original data in 2
fields. Customer and predicted value, but they need to be prioritized.

1 needs to be the one with the lowest current and highest potential.
2 needs to be the 2nd lowest current and 2nd highest potential......so on.
Then I need to get the highest current and highest potential.......so on.

I know that what I require is quite complex and it may have to be via
calculations, but I am hoping that excel can do this?

View 13 Replies View Related

Limiting Output Based On Cell Data?

Mar 22, 2013

I've got a worksheet that needs to be limited by the value in a cell. I've attached it so you can look at it and understand what I need better. In I7 (red box) is a number that is the max a machine can rotate. B6 should never exceed this and B9 should be scaled down to match the output of B6. I think B9 will scale down automatically from the existing formulas if B6 is throttled correctly.

Should =FLOOR((((F5*D11)/D9)*3.82),50) have a max value equal to I7? And how would I incorporate it?

View 1 Replies View Related

VLookup To Output Text Data Results?

Dec 24, 2013

I have a column with lots of tube station names. I have a look up table with the tube station name, and the possible lines that pass by that tube station.

One tube station may be covered by more than one tube line.

I am looking for a formula that will output every tube line that caters that one tube station.

first question, what is the best way to organise the data in my look up table.
Second, is a vlookup the best formula to achieve the output I want? i.e. Reflect how many tube lines cover each station

see table below: If the station name has only one tube line, Vlookup works fine. When the station, such as Canno Street, has two tube lines, Vlookup only picks up one tube line. If I duplicate Cannot street under the station name, the Vlookup picks only one tube line.

LOOK UP TABLE A1 - station name
B1 Tube line name
Cannon Street
District

[Code].....

Is there a formula that will output the data of the second tube line name in column D1?

View 3 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

Dynamic Data Validation To Determine SUM Related Output?

Dec 2, 2013

Trying to set up a dynamic data validation, where each of the subsequent data validation boxes change based on the selection in the first data validation box (while maintaining 4 parameters within a cell, except for the last one).

I have used the IF-statements to determine the "Week's SUM" in H5.

What I'd like to do next is to determine a formula in the "Week's SUM" cells (H6,H7,H8) based on the choices in data validation boxes "Week #" (G6,G7,G8) with the following rules in place:

1. If I made a choice in G5 to be "1" which caused data validation in cell G6 to update, then in G6 I choose "2", the output in H6 should be just week 2's value of 20 (not the sum of week 1 and 2).

2. However, if I choose "3" in G6 (after I chose "1" in G5, as above) then I'd like the output in H6 to be the SUM of week's 2 AND 3, if that makes sense and so on. (e.g. If I chose "5" in G6, then output in H6 should be the SUM of week's 2 AND 3 AND 4 AND 5).

Same rules apply for H7 and H8. I have a feeling this may involve MATCH function and INDIRECT or SUMIFS but how to approach this.

I've attached a file : Dynamic DV and Dynamic SUMS.xlsx‎

View 3 Replies View Related

Select Worksheet Using Combobox With Colored Data As Output?

Jul 15, 2014

I get a macro which will provide me coloured data by eliminating the non-cloured(with white) data. Sheet1 has a combox of worksheet within an excel sheet which is listing all the worksheet in an excel sheet.By selecting the worksheet name(for example sheet1, sheet2.....sheet26) and I can move to worksheet I selected and can view the data(coloured one).I have around 25 excel worksheet within an excel sheet and each sheet has various different kind of colors but I dont want the output to show non coloured data after I select the worksheet in Combo box.

View 3 Replies View Related







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