Formula That Returns Multiple Values With Numbers That Are Between Range
Jan 9, 2014I am trying to return several values >10K, >5K, >3K, >1K,
View 7 RepliesI am trying to return several values >10K, >5K, >3K, >1K,
View 7 RepliesI would like a formula to add up values in Col E based on a range of account numbers in Col C
For eg adding up Sales values in Col E based on Account number being series 3000-3015 in Col C (this particular series must exclude numbers ending in an alpha). I also have account numbers ending in an Alpha where I need to add up the values pertaining to these for eg 3000D-3015D, 3000K-3015K etc.
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
I've never quite mastered the idea of passing arguments between subs and functions. I have a project right now where I am writing this same bit of code over and over, for different parts of the macro:
View 4 Replies View RelatedI'm trying to do the next level of a v-lookup. I have a group of data with a text "flag" on certain rows. On my summary worksheet, I'm trying to do a lookup or a nested index function retrun all of the values with the text flag next to it.
I've been playing around with nested formulas and this is where I'm stuck. Here's what I came up with:
=INDEX($D$1:$E$9,(SMALL(IF($D$1:$D$9=$A$1,ROW($D$1:$D$9),ROW(1:1)),ROW(1:1))),2)
This only works for one row and not for the other rows. Maybe I'm going down the wrong path?
i have a workbook that draws numbers from other workbooks.For some reason every time i close and reopen it the fields are blank.I have to go in and open each workbook for the cells to populate.
View 1 Replies View RelatedI have three different values in three different columns and I want to find the maximum amongst the 3 values.
A1 B1 C1
100 50 40
in D1 I have written a formula
=MAX(A1, B1, C1)
But it is returning an error saying formula you typed contains an error
I have a mass of data which look something like this:
table removed
and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.
So basically for example I would want the top row to now read:
table removed
and then apply the same procedure to the other ranges below this.
following issue:
The following table is given:
flower
20
rose
flower
21
rose
[Code] ........
Which needs to be turned into:
flower
20
22
rose
flower
31
32
blossom
tree
1
3
apple
The last column is the one that dictates when a new range of numbers start. There should be one range of numbers for Rose, One range for Blossom etc.
I am looking for a formula that returns the cell address of the last cell > 0 in a range.
in the example: A1CLA23 8
4546557528
the result should be A7.
I have a spreadsheet with a list of about 3800 lines of dollar values in "K" that correspond to various serial numbers in "E".
These serial numbers come up multiple times at different place throughout the 3800 lines.
I know by using SUMIF I can calculate the total value of each serial number, but is there a way to bring the serial numbers across to correspond to the sum of the dollar values?
I want to build a database where the user can select a specific time period. I have a static file with the time periods and the related values. For instance I have in column A1:A15 the time periods from January 2011 to Dezember 2011, but I have some dates multiple times as there are more values attached to it. For instance I have in the first 4 rows January 2011...what I want is when someone puts in the value January 2011 in cell B1 and Dezember 2011 in cell B2 that the whole static file gets copied to another location (including the multiple dates) displaying the chosen time period. Similarly if someone puts in the value March 2011 to November 2011, I want only those values to be copied.
View 4 Replies View RelatedI have some problem with my data here. I have created a formula to compare the numeric value between the column. I want to have the results in the column but the results I have is wrong. Did i did something wrong with the formula?
I wanna compare the value from A2 and C2, B2 and D2. If the results is correct, then is will display true value in e2 else will display false value in e2.
when i compare 2.3oz and 2.25oz, the value displayed is true which is wrong, it should display "false"
I have attached the file here...
I need a formula that calculates the values of 5 seperate cells located in different places throughout my sheet. The value will either be numerical or display a text value (normally "RD"). If it displays text I do not want that value counted. So it literally just totals the number values.
View 7 Replies View RelatedHOW DO i WRITE A FORMULA FOR 0 TO 5000 = 2% 5001 TO 10000 = 1% and over 10000 = .5%
View 2 Replies View RelatedI'm making an eligibility chart based on income. If 1 person (equals a
household) makes between $0 and $1000 (household income) they are in "x"
category. if that person makes between $1001 and $2000, they are in "y"
category. If that person exceeds $2000, they are in "z" category. So I have
3 categories they can be included in (x=Free, y=reduced, z=paid...it isn't
just a true and false statement). The tricky part is the number of people in
the household. 2 to infinite # of people making in the range of $0-$1000
need to be in "free" category. 2 cells are being drawn from. A1 is the
number of people, A2 is the income, A3 is the formula for figuring their
eligibility. I need A3 to say "Free", or "Reduced", or "Paid" as the end
result of the data.
Is there a formula that would allow you to take the average of all values within a range but not count the zero values? I thought something like this might work but it's not. Neither one worked.
=AVERAGEIF($E$4:$E$34,">0")
=AVERAGEIF(E4:E34,">0")
I'd like to take data in the range from B2:B500 and in C2 sum from B2:B9 and then in C3 sum from B10:B17 and in C4 sum from C18:C25 and so on.
View 2 Replies View RelatedEvery month in Column A I will add 2 numbers to my range. I want my formula or macro to sum the last two numbers in that column
View 1 Replies View RelatedI cannot find the right catagorty I need to use to word this. I know there is a formula out there. I basically have 1k numbers I need to add into Collumn A of a worksheet - the numbers are from range 34-2501 through 34-3500. How can I get excel or VBA to do this automatically?
34-2501
34-2502
34-2503
repeat til
34-3500
Also, how to make it user friendly to create the same process over and over with a different set of numbers... meaning make it so I onlt have to input the range or numbers over and over. if it is an easy task I do this allot
I'm trying to find out if there is a way to count a specific number from a cell that has multiple numbers in the cell. I have attached a worksheet.
Example, on the attached worksheet, I want to count how many 16's in column C and input that total number into G18. Then I want G19 to tell me how many 17's in Column C, then how many 18's in G20 and so on. I want to do this for all days in a month, 1 through 31. If there are no numbers, then "0" or a blank cell is ok. Not to concerned with zero's.
Attached Files: xlsx Book2.xlsx
I have a long list or work items that I track on a log on daily basis.Some work items are repeated twice or trice to input a certain comment.Therefore, when I try to calculate the total number of work items for the day using counta formula, if results in an inaccurate total since certain work items are repeated twice or trice. The work items consist of a 8 digit number.Example 8876098.
View 4 Replies View RelatedI have an Excel workbook with around 100 worksheets. All of them have similar columns, but the values are stored as text. The length of columns and missingness is different in different worksheets. I will be grateful if any of you could suggest a VBA that could convert the numbers stored as text back to numbers. I have been looking for an answer for a while, but cant seem to find one that automatically looks at every worksheet and converts text into number.
View 1 Replies View RelatedExcel 2010, I have the following list (showing part of it):
Name
T-Shirt Sizes
Nathan
S
[Code]....
The aim of the list is to see what Sizes each and every person needs and then count how many T-Shirts are required for that particular size. The list is over a 100 entries long so counting it manually isn't really an option as it's time consuming as well as errors might arise.
Thus, I've been trying to use the IF statement with the following logic but to as no avail
IF Size in Column is XS
Add 1 to a particular Cell
IF Size in Colum is S
Add 1 to a particular Different Cell
and so on for the other sizes...kind of like a counter for a for-do-loop.
OK, I have a huge SS with lots of named ranges. Many of which are dynamically assigned lists.
All of the existing ones work fine, but when try to add a new named range, it returns the cells from a previously named range. (Always the same old one.)
example:
A range (one of many) is named "Shift_List" and is defined using
=OFFSET(info!$C$3, 0, 0, COUNTA(info!$C$3:$C$2000),1)
New range is created named "PN_List" and is defines using
=OFFSET(info!AA$3, 0, 0, COUNTA(info!AA$3:AA$2000),1)
When I create a Data Validation List or otherwise use "Shift_List" as the source it works fine.
However if I do the same thing and refer to "PN_List", it returns the items from "Shift_List"
Any new named range returns the Shift_List cells, although older ones still work correctly.
Im working on a dashboard for my department and its going pretty well. Im stuck on the Project tracker.
On the front page (dashboard tab) I want to see all overdue projects from the Project Tab. I have the Overdue status working on the project tracker so I need some kind of multiple return vlookup for all of the OVERDUE tagged rows. This can be a macro or auto fill formula.. if someone has seen something similar, i would love a link so i can edit it accordingly.
I have a simple code that opens 2 workbooks and then copies and pastes a cell from one to the other. The cell that's being copied is a formula by the way.
My problem is that the copy and paste keeps returning a "0" value, instead of the correct number. I am thinking it's probably because the file from which it is copied is very large and because the operation barely takes a second, it doesn't allow it enough time for the formula int he cell that is being copied to calculate the value.
I am trying to write a formula that will give more than 1 return for a vlookup statement.
The info i am looking up is sku code (col a) and expiry date (col b).
There are more than one entry for some skus , but I am only getting returned the first expiry date.
I have a raw data page which contians peoples name and where they charged thier time. My problem is that I only want thier name to appear once. What type of formula do i need to use to make this happen.
Ex
Employee name Project No. WBS Code Hours
John Smith 123456 1 4
Jane Doe 123456 1 2
John Smith 123456 2 2
Jane Doe 123456 2 1
John Smith 123456 3 2
Jane Doe 123456 3 5
At the end I want to do a count to let me know how many people I have charging the job but I don't know what formula I need to exclude duplicate entries.
I need to lookup a Order Number and return all associated Item Numbers. My spreadsheet has about 10,000 lines and these Order Numbers could have as little as 1 or as many as 15 Item Numbers associated. I would like to have a drop down on each Order Number that shows the Item Numbers.
View 3 Replies View Related