Sum Col B Based On Col A Value With Result In C?
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
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
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
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
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
Aug 5, 2014
How to formulate results of students in excel sheet.
From the attached picture (capture1.jpg) of the excel sheet - The rules of exams are:
1. if candidate scores 50% in all 4 papers, PASS and proceed to next year
2. if candidate scores 50% in 2 or 3 papers + borderline fail in 1 or 2 papers, VIVA VOCE exam for the borderline failed paper (Definition of borderline fail is candidate scoring 45 to 50 marks)
3. if candidate scores 50% in upto 2 papers + borderline fail in more than 2 papers, RE-EXAM
4. if candidate scores 45% in 2 or more papers, FAIL and repeat the year
The rules are in the attached picture flowchart.jpg
I am unsuccessful in writing a formula for such multiple criteria...
View 12 Replies
View Related
Jan 13, 2014
I am trying to create a formula that is able to calculate an end result based on the cell contents of 3 columns, the results are predefined in 3 other columns, here D, E & F
For example:
Column A Column B Column C Column D Column E Column F Column G (Results)
Royal Mail 1 100 2.8 2.3 1.2
Royal Mail 2 100 2.9 2.4 1.3
Royal Mail 3 100 3.0 2.5 1.4
DPD 5 200 4.5 2.8 1.5
DPOST 1 100 1.2 3.2 1.7
I am trying to create a calculation that in Column G will work out, IF Column A=Royal Mail AND Column B=1 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=2 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=3 AND Column C<=100 THEN [@[Column F]], IF Column A=DPD AND Column B=5 AND Column C<=200 THEN [@[Column E]], IF Column A=DPOST AND Column B=1 AND Column C<=100 THEN [@[Column D]]
Here are an example of what I have tried, amongst many...
=IF(AND(AND([@[Default Post Postal Service]]="Royal Mail"),AND([@[Default Postal Format]]=1),AND([@Weight]<=100)),1,0) Everything is zero.
View 4 Replies
View Related
Feb 24, 2014
My current problem is that I need to search two tabs of data and use the results to modify the contents of one cell in the first tab.
I'll try to be as clear as possible:
tab1 has relevant cells "a" and "b", same row
tab2 has 3 relevant cells "c", "d", and "e", all on the same row
All cells are text values - some digits, but should be treated as a regular String.
The search needs to look at cells "a" and "b", find their match in "c" and "d", then replace the original value of "b" with the value in "e" after a match has been found.
View 1 Replies
View Related
Nov 26, 2009
I have a problem with a calculation i am trying to do. I have to calculate holiday allocation for staff but i my company also offers length of service bonus days. After 5 years you get 1 extra day, after 10 years you get 2, after 15 you get 3 and so on. In my sheet i have a formula based on their start date that tells me their length of service in the following format: 10 years, 2 months.
I have put an IF Statement in for each of the different milestone criteria but the result is not coming back so i know i am missing something. Is there something wrong with the formula itself or is there a better one that will allow me to put the start and end of the range i want it to look at? For example can i tell it to say if between 5 years and 9 years 11 months put 1, if between 10 years and 14 years 11 months put 2 etc in column H.
View 3 Replies
View Related
