Get Another Value Of Row Based On DMIN Result?
Sep 9, 2012
I've got a database with 8 columns and many records. The task is to find the value in column 3 for the smallest number in column 7 with additional criterias for columns 4 & 5.
I've used the DMIN function for finding the smallest number, but I don't know how to show the corresponding value in column 3. I think I should only use DB functions. (I've checked and there is exactly one line with the set of criterias above, so there should be no errors.)
Is there any way I can specify to DGET that I want to get the minimum of a column but based on the other criterias? Or embed DB functions into one another?
View 7 Replies
ADVERTISEMENT
Dec 4, 2009
I have 4 categories A, B, C & D. These are in desending importance, means A is most important and D is least important. Now there could be many A, B, C & Ds listed in a column. The challange is if coulmn contains A anywhere then the result should be A. If A is absent, then search for B, if present anywhere then display the result as B. It doesn't matter how many times A or any character is listed in column. I am attaching a sheet for better explanation.
View 4 Replies
View Related
Aug 25, 2009
I currenlty have a spreadsheet that works well but uses a lot of array formulas and, therefore, is running VERY slowly. The slow speed makes it impractical to work with, so I am trying to replace the array approach with the DMIN function. However, I am having trouble getting the DMIN function to find the minimum value in a column range according to a criteria in the same arrays did for rows.
Here is my problem:
I currently have arrays (composed of dates) in rows and I have converted this to non-array columns. I have pasted a sample of the new worksheet below.
Column D ["Original End"] has dates that are static. Column F ["New Start"] has dates that can change according to user inputs. I want Column G to find the date in a Column D that is greater than the date in Column F in the same row.
In the table below, you can see that I was able to get this to work for cell G3; however, when I pull the formula down, it stops working. Here is the formula I have in cell G3: =DMIN($B$2:$E$15,"End",$I$2:J3).
Conceptually, I want to write the following function in cell G4: =DMIN($B$2:$E$15,"End",">"&F4), where the criteria is 'greater than date in F4' - but this obviously doesn't work.
View 9 Replies
View Related
Jan 19, 2007
I would like to return the lowest non-zero number in the second column (B), based on the value in column A
in this example, 1 should come back
Example numbers
A B
22 0
33 0
22 1
22 2
33 2
22 3
I started out using:
=IF(AND($A$1:$A$6="22",$B$1:$B$6<>0),MIN($B$1:$B$6),"")
But it is not working for me.
View 9 Replies
View Related
Dec 3, 2008
I'm using the formula: =IF(G28>VLOOKUP(D28,MileageLookup,2,FALSE),G28-VLOOKUP(D28,MileageLookup,2,FALSE),"0") in my "Overage" column[H28] to look up weekly mileage limits for employees and calculate by how much(if any) the "Actual Mileage"[G28] exceeds this limit. If there is no overage for the employee that week, I've asked it to return a 0 so that the next column can use this result to calculate the "Overage Charge"[I28] which based on a rate in the MileageLookup table. Both formulae are working properly.
The problem comes when I try to use the DAVERAGE and DMIN functions on the "Overage" column. Excel doesn't seem to recognize that the 0's are actually legitimate zero values and not just some sort of place holder even though it calculates the "overage charge" properly and recognizes the 0's.
View 3 Replies
View Related
Jan 7, 2008
I'm new to the DMIN function and I'm having a problem.
Cell A2: Date (rest of column is date format) (heading for the column)
Cell A2: Credit (rest of column is acctg. format) (heading for the column)
Cell A2: Debit (rest of column is acctg. format) (heading for the column)
Cell A2: Balance (rest of column is, equals balance field from previous row + credit - debit for current row, acctg. format) (heading for the column)
Column E: Notes (rest of column is generic text) (heading for the column)
Cell K1: Date (the text "Date")
Cell K2: > TODAY() (the text "> TODAY()") -- maybe this should be ="> TODAY()"?
Cell H2: =DMIN(A2:D1000,"Date",K1:K2)
I'm trying to get the minimum balance for the Balance column where the date is greater than today (lowest balance that I have in the future, which helps me see if I will be overdrafting).
I'm getting a value of 0 in H2. I have also tried =DMIN(A2:D1000,1,K1:K2) and get the same result.
View 9 Replies
View Related
Nov 30, 2013
What would be the formula in C to sum the numbers adjacent to A where the 47's in A = 36 from all the adjacent numbers in column B, all the 55's = 32 from all the adjacent numbers in B, and 18 = 12 as it is the only number adjacent to 18 in B.
When code is run the numbers will change in both columns (random numbers) so the next time there may not be any 47's or 55's in A. And new numbers in B to sum.
As I ponder this, I'm thinking it may require code, which is okay.
AB
553
477
5515
428
4710
476
4713
488
420
1812
5514
4815
View 3 Replies
View Related
May 20, 2009
New to the forum and in need of a bit of help. Friend of mine is in here regularly looking for assistance and tells me this is the best excel forum around. Thought I would put it to the test with a problem that is doing my head in.
The following is a table which shows a salesmans sales. He or she has sold to 8 customers.
What I need to do is show in the commission column how much they should get paid for the additional products based on this criteria.
1. If the penetration is equal to or over 41%, and he has sold the additional product for max profit of 250 then he should get £40
2. If the penetration is less than 41%, and he has sold the additional product for max profit of 250 then he should get £25
3. If he hasnt sold the product for full amount then he gets 10% of the profit, regardless of the penetration ...
View 6 Replies
View Related
May 8, 2014
see the sheet attached. To put into context, I am a teacher and want students to work on tasks, and have the answers to those tasks available to them but hidden, until a give them a password that unhides it.
On the attached document, Lesson1! K35:Q41 i have some answers to the task beside it in B35:H40. I want to hide these answers in some way (without affecting the columns as there is data above) and then when I want students to see the answer I give it to them and they enter it into L43 in this example. At this point the answers are revealed and stay revealed for revision purposes.
This will be repeated using different passwords for different tasks throughout the workbook e.g. L52 will be a different password revealing the answer in K48.
View 6 Replies
View Related
Feb 13, 2007
Each car has a value, and each car has odds.
I must choose 5 cars, and the value cannot exceed 100.
I typed in all my data, can Excel choose the best possible lineup for me?
In the end, I need the lowest result possible while only using 100 pts in
value.
I am using Excel 2003
Here is a sample of my data.
Car # Value Odds (#to1) (Result=Value x Odds)
123.5494.00
223.45117.00
322.85.75131.10
422.66.75152.55
522.212.5277.50
62315345.00
71523.5352.50
82217.5385.00
921.820436.00
1019.523.5458.25
1121.623.5507.60
122125525.00
1321.725542.50
141930570.00
1522.130663.00
1620.335710.50
1721.934744.60
182040800.00
1918.845846.00
2018.647.5883.50
View 9 Replies
View Related
Mar 3, 2007
Any other day but today (I've been starring at numbers too long) I could do this.
I need a simple formula that will return:
a. 3 if resut is >= $2.5M
b. 2 if over >= $0.5M
c. 1 if
View 9 Replies
View Related
Jul 25, 2006
I'm looking to set up a combo box with different year options and add functionality so the data that can be seen on the sheet at a given time is driven by the combo box year value. (functionality somewhat like a webpage, where once you choose a certain value from a drop down box, you see data corresponding to the value)
View 9 Replies
View Related
Oct 27, 2006
if Cell F42 contains 50%, then my fomula returns "wrong"
=IF(AND(0%<F42,F42<50%),"F",IF(AND(50%<F42,F42<60%),"D",IF(AND(60%<F42,F42<70%),"C",IF(AND(70%<F42,F42<80%),"B",IF(AND(80%<F42,F42<100%),"A","wrong")))))
how do I get the formula calculate correctly?
View 7 Replies
View Related
Jan 27, 2008
i have the formula "= sum(A1:A10)" in cell A4 & would like to call macro when the value changes. The code i'm using below work's if I manually type in a value, but isn't working with the formula.
Private Sub Worksheet_Change(ByVal Target As Range)
' Checks for cell value change
If Intersect(Target, Range("A4")) Is Nothing Then
Exit Sub
' If cell value change, calls msgbox based on criteria
Else
If Range("E4").Value = "C" And Range("A4").Value > "30" Then
Call MsgBox1
End If
If Range("E4").Value = "F" And Range("A4").Value > "38" Then
Call MsgBox2
End If
End If
End Sub
View 4 Replies
View Related
May 28, 2008
See the attached file. I think index and match may help me having done a search several times in these forums, but having been trying to do this for several hours I am just struggling to understand each part of the formula unfortunately. I have a list of data (cellsA1 to C33) which I need to use as my source for the information that is displayed in cell I2 based on the two selections made in cells F2 and G2.
I can't use filter and data sort in this situation - I am creating a spreadsheet for someone who truly knows nothing about excel, so I need this to be as simple as pointing and clicking at cells F2 and G2 and the formula doing all other work. Is it possible to do what I am asking for (hopefully the fact that peoples' names are repeated and months are repeated won't cause a problem?
View 4 Replies
View Related
Dec 13, 2012
I have a scorecard that looks something like this:
90% or greater=3
80%-89%=2
70%-79%=1
0%-69%=0
I need the "cell" to react accordingly and I'm lost.
View 2 Replies
View Related
Sep 19, 2009
I have been trying this for hours but to no avail.
I have a table with 4 columns headers
Name, Amount, Loc and Code
The name may look like ABC 1, ABC 2....
The Loc may be in US, GB...
and the Code may be AA, BB
I need to return a result "Y" if the sum of the amount is > 100
and "N" if the sum of the amount < 100 based on the conditions
of the following :if
1) Name is the same entity, such as ABC 1 and ABC 2 and
2) Loc is the same, US..and
3) Code is the same
I have attached a sample to illustrates the result
View 10 Replies
View Related
Mar 5, 2010
I am trying to make a drop down list based on the result of a vlookup.
What i want to do is look into a table that has country name, depot station, but i have more than one depot per country so when i look up with vlookup i only get one result back, the table looks like this.
Country Country nameDepot code Depot name 1 GBUnited Kingdom STN Stansted 2 GBUnited Kingdom EDI Edinburgh 3 GB United Kingdom EMA East midlands 4 FR France
GNO Garanoa 5 FR France MRS Marseille
How can i look up GB or United Kingdom and get all the depots listed from that country.
View 9 Replies
View Related
Aug 1, 2006
how to have a cell equate to an autoshape based on the results of an "if" statement?
View 4 Replies
View Related
Oct 17, 2006
I have four columns, A through D
Column C is returning a simple vlookup of A
I need Column D to return a value where C is TDMA return TDMA or when C is GSM lookup column B compare to tab2 (columns A through L) returning column 12.
View 5 Replies
View Related
Jan 4, 2007
I would like to use formulas to display different results eg UNDERBUDGET - OK - OVERLIMIT etc. Ideally they would be shown using different colours & text size. I can't seem to change the colour/text size inside the formula for the different words - only for the whole cell.
View 2 Replies
View Related
Oct 2, 2007
I have a table of repair jobs done over a period of time. I just need to determine during which shift was the repair job done. The time during which the job was initiated is called Notification Time.
If the job was done at, say, 0100 hrs, then it was done during Shift 1 (0000hrs to 0800 hrs)
If done at 0830 hrs, Shift 2 (0801 to 1600hrs)
If done at 2030 hrs, Shift 3 (1601 to 2359 hrs)
I need a formula to allow me to determine the Shift no. by just checking against the Notification Time column.
View 9 Replies
View Related
Oct 26, 2007
I have a spreadsheet where in cell E2 there is a drop down box with the following options to select: Warranty Replacement, Insurance Claim, Billing Issues, Retention Opportunities.
In cell F2 is where wait time minutes are generated depending on what is selected from the drop down box in E2. I am trying to create a nested IF formula for the following scenario:
Warranty Replacement = 20
Insurance Claim = 20
Billing Issues = 15
Retention Opportunities = 20
View 5 Replies
View Related
Dec 18, 2007
I need an excel function for this...
Say,
Cell A2= 8
Cell A3= 1
If A2 < (9*A3), then Cell A4 will say "Plastic", otherwise it will look to the next function, which is If A2 < (10*A3), then A4 will say "Compact", otherwise, it will look to the next one which is If A2 < (15*A3), then A4 will say "Semi-Compact" otherwise If A2> (15*A2), it'll say "Slender". so at the end, Cell A4 can only be one of these: 'Plastic' or 'Compact' or 'Semi-compact' or 'Slender'. How can i combine all the functions together?
View 2 Replies
View Related
Jan 12, 2008
I would like to gray out or hide contents of a series or group of cells when a particular cell has a certain result. i.e. if cell A1 is <5 I want cell rows 5 thru 10 to be hidden or grayed out.
View 5 Replies
View Related
Jan 17, 2008
I'm attempting to do the following:
A1 contains names of projects.
B1 contains variances from budgets, being positive or negative numbers.
C1 will contain results.
I need to know which of the B1 cells contain a number equal to or greater than 20%/-20% and output the names in A1 that match the criteria to C3.
View 8 Replies
View Related
Feb 4, 2008
I am currently looking at the workings of a spreadsheet designed by someone else.
First of all i need to know,how these combo boxes are created in the attached sheet,because it cant be edited.in addition to tht in the coloured cell (F17)i'm trying to dereive a formula which is,if (C17 = doll "1"),but its not working.Please someone give me a solution.
View 9 Replies
View Related
Feb 15, 2008
I want to make a formula based on 2 times ex: 10:07:00 and 10:09:00. This formula should display "intime" if the diference between both is under 20 minutes, "outime" if the diference is above 20 minutes and it should display "error" if the time is under 0 minutes (this will only happen when someone makes a mistake typing in the time. For example 10:37:00 and 10:36:00)
View 3 Replies
View Related
Aug 2, 2008
I have a cell with the following formula: =IF(ISERROR(AVERAGE(A2,C2,D2)),"",(AVERAGE(A2,C2,D2)). I'm using this formula because I want to average the selected cells, but if A2,C2, or D2 are blank I don't want it to return the # DIV/0! error, hence the "ISERROR" part.
Now... I want to conditionally format the cell with the above formula in it so that if it is equal to or greater than 80% it's green. Here's the problem, the conditional format colors the cell green if it is blank too. How do I stop that?
Below is an attached spreadsheet to show what I'm trying to do.
View 3 Replies
View Related
May 31, 2014
When I enter barcode X in cell A1, I want B1 to say supplier and C1 to say product name and D1 to say package quantaty etc.
And if I enter barcode Y in cell A2, I want B2 to say different supplier, and so on, hopefully you get the picture.
View 7 Replies
View Related
