Conditional Formula Assistance (default To "training Required" Rather Than The Value It Would Give)
Oct 28, 2009
=ROUND(SUMIF($C$26:$C$106,D126, $F$26:$F$106)/COUNTIF($C$26:$C$106, D126), 0). the formula is from a sheet which is used as like a questioning/ skills matrix.
The reference of d126 is a category eg: complaint handling and there might be 4 questions on complaint handling each of the answers being displayed as a number between the cells f26-106. If any of these individual cell results are equal to 0 or 1 then i want the overall answer for the formula to default to "training required" rather than the value it would give.
View 4 Replies
ADVERTISEMENT
May 1, 2006
the scenario is a spreadsheet for adding call centre operators evaluations (1 being worst and 5 being best) from the clients. in the spreadsheet, i need to create a function to add a quick evaluation based on the operators score from the client
so if the score is 4 or higher it would read "good - improving"
and if it is less than 4 "poor - improving"
i think this is just a simple IF question...but i have forgotten how to do it
View 3 Replies
View Related
Mar 28, 2014
I manage a number if individuals in the Army and as such, track their annual training requirements. Some requirements are semi-annual, annual, quadrennial, etc...
I have a spreadsheet to track the dates that the requirement was last completed (attached). I would like to use conditional formatting to turn dates that are within 30 days of being due (or blank) RED and dates that are between 31 days and 60 days of being due YELLOW.
I am using Excel MAC 2011.
View 3 Replies
View Related
Apr 12, 2007
I only grasp the basics of loops and not any experience w/ formulas in VBA to date.
For each item in col "I" run this formula (the cell references are relative)
=RIGHT(I6,LEN(I6)-FIND(" ",I6))
I am only at this point so far:
Sub ItemName()
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Integer
Dim LRow As Long
Set ws = Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("Record Creator")
LRow = ws.Cells(Rows.Count, 9).End(xlUp).Row
For i = 6 To LRow
=RIGHT(I6,LEN(I6)-FIND(" ",I6))
End Sub
The basic function of the loop is to Delete the first term of each cell in the range including the first space.
example:
Enjoi Barletta Deck
Resulting in:
Barletta Deck
View 9 Replies
View Related
Jun 15, 2007
I get a "#value" error message when I utilize the formula noted below. I seperated it into distinct sections so that it is easier to view. Basically, what the formula is doing is determining whether if two values are the same, then take the absolute difference of the average of other values ,excluding one of the previously noted values, and compare that difference to a different parameter. If the difference does not exceed the parameter, then calculate the difference; otherwise the ending result is zero. Pls. note that the error seems to occur when I input the fifth section into the formula.
=IF(K23="","",IF(AND(K23=E23,ABS(B23-AVERAGE(F23:J23))>B20),B23-ABS(AVERAGE(F23:J23)-B20),
IF(AND(K23=F23,ABS(B23-AVERAGE(E23,G23,H23,I23,J23))>B20),B23-ABS(AVERAGE(E23,G23,H23,I23,J23)-B20),
IF(AND(K23=G23,ABS(B23-AVERAGE(E23,F23,H23,I23,J23))>B20),B23-ABS(AVERAGE(E23,F23,H23,I23,J23)-B20),
IF(AND(K23=H23,ABS(B23-AVERAGE(E23,F23,G23,I23,J23))>B20),B23-ABS(AVERAGE(E23,F23,G23,I23,J23)-B20),
IF(AND(K23=I23,ABS(B23-AVERAGE(E23,F23,G23,H23,J23))>B20),B23-ABS(AVERAGE(E23,F23,G23,H23,J23)-B20),
IF(AND(K23=J23,ABS(B23-AVERAGE(E23:I23))>B20),B23-ABS(AVERAGE(E23:I23)-B20),0))))))))
View 9 Replies
View Related
Aug 4, 2009
Current Formula
{=IF(AND(G144>0,F144:F173
View 9 Replies
View Related
Feb 2, 2009
I need to create a formula that looks at 2 adjacent cells. If both cells show 'YES' then I need the 3 cell to show 'YES'. If either/both of the 2 adjacent cells are blank then the third needs to be blank.
View 2 Replies
View Related
Oct 3, 2009
If a cell cntent includes a symbol [as an arrow, for instance] beside the text,the font colour does not change as required when the condition is met.
View 2 Replies
View Related
Sep 21, 2008
I am interested to get some opinions/input on where one might obtain Excel training/classes, etc. Our local college infrequently offers classes and when they do, the class is most always entry level which I have already taken. Online or correspondence would be acceptable.
View 2 Replies
View Related
Aug 30, 2007
My boss is making me teach an Intermediate Execl class that will last about 55 min to a wide skilled class. Does anyone have any suggestions on what to show the group. I was think of 4 topics but not sure.
View 6 Replies
View Related
Nov 20, 2008
I have a report of users and thier roles
User# User_Christian User_Surname Role
1 bob smith operator
1 bob smith supervisor
1 bob smith warehouseman
2 john stone operator
2 john stone gunman
5 george brown Foreman
What Im after is:-
a matrix with unique Role list across the top
Unique ID & Name down the side
(I can do all the above using recorded macro's)
but the clever bit I cant even start to work out is how do I get matrix populated from the list?
View 10 Replies
View Related
Dec 18, 2012
I am trying to create a shared database that tracks training for about 100 people. I've built a workable sheet, but the only way I have to share it over e-mail (there is no office NAS set up, and we're on a military base so we can't establish wifi).
Is there a way to set up a workbook that can be updated on one computer and instantly reflect that information in the cloud?
My goal is to have each team responsible for updating their own form and then have that information update my master tracker.
View 5 Replies
View Related
Oct 27, 2005
The following formula shoud give an answer of 178. I think I am placing the
parenthesis in the wrong position.
The answer I get is 308669
=(((F22*71449)*0.4)+16147/C22)
Cell-F22 number is 1826
71449 is a constant number
0.4 is a constant number
16147 is a constant number
Cell-C22 is 10.8
View 10 Replies
View Related
Apr 8, 2009
I have 6 rows (A-F) with dollar vallues. I am trying to create a formula on row G that will give me the highest dollar value out of rows A-F.
View 9 Replies
View Related
Jun 30, 2009
I’m looking for a formula that will give me a figure against the name in column A (name) while looking picking out a specific word within the column B, say "alarm"
So it reads
Alan Ingram 1
David walker 3
And so on...
View 9 Replies
View Related
Jun 8, 2007
I am trying to do something for a baseball game I am creating. What I am looking for is a formula for one cell that will basically give me a whole number when a certain criteria is met. I am also looking for this cell's formula to get part of it's formula from another cell in the document. So let's use cell A1 has 1.50 in it.
I want this other cells formula to be able to give me a 9 if A1 is 0.00-1.50, a 8 if A1 is 1.51-2.79, 7 if A1 is 2.80-4.30, a 6 if A1 is 4.31-5.49 and 5 if A1 is 5.50-500.00.
View 6 Replies
View Related
Apr 25, 2013
I am trying to copy formula as follows:
2
3
1
10
=A4*B4*D2
=A4*B4*D3
[Code] ......
I'm trying to copy the formula both down and across but I'm guessing both issues are the same.
View 3 Replies
View Related
Jan 13, 2014
Is there a lookup formula that will give me multiple items? so in my example attachment i want to look up risk from section A to give me the item in section B, but for risk 7 for example it has multiple items (a,b,c), how would i get all them? even better would be if its possible to have in seperate cells
View 4 Replies
View Related
Jul 26, 2014
I'm trying to write a formula that searches for the name of the column on one sheet, finds that column on another, then results in the column letter. So, ideally, if the first three columns in the first tab were "Team," "Name," and "Total," respectively, I just want a formula to search for "Name" and give me back "B."
I want to do this in case anybody ever moves the "Name" column, for example, from column B to column C -- so then it would search for "Name" again and change the range in the formula to "C" because that's where that data lives now.
Currently, I'm using it for a COUNTIF() function that is a very simple: =countif(indirect("'"&$A2&"'!K:K"),$B2), where A2 is the sheet name. The latter half of $B2 is fine -- but I would love to have a more complicated formula take the place of the range "B:B" in case the "Name" column ever got moved.
View 8 Replies
View Related
Aug 1, 2008
i have =TIME(LEFT(F17,FIND("h",F17)-1),RIGHT(F17,2),0). which gives a time in the format 9:30PM, or 10:30AM etc. How would i change the formula to give me a time minus 15 mins. Eg, if the above formula gave me 8:45PM, i would want it to show 8:30PM
View 4 Replies
View Related
Jan 21, 2010
I know that I an probably asking for too much but I must get the formula some way. OK, I need a formula for this:
lets say I have 3 columns (A,B,C (search criteria). In each Column there is a DIFFERENT number. There is 3 more columns (D,E,F (used to compare).Each column has 3 Different numbers. If 3 numbers match between the two sets of 3 numbers I want Column G to display "CCC". IF 2 numbers match I want G to display "CCH". If 1 number is a match I want G to display "HHC". If none of the numbers match I want G to display "HHH".
I can manually do these but it will take waaaay toooooooooo much time to do. If you can do this for me it would be a great help indeed because I'm trying to do other states. With this formula I can use Automate to do a large quantity of numbers.
View 14 Replies
View Related
Jan 26, 2010
1. The example is cell B1 has a drop down list with the following options: MIN, MAX, AVERAGE, MEAN, SUM
The range F2:F20 hold the values upon which I want the formula to work
Cell B3 is to calculate the result of performing the the function defined in B1 over the data set in F2:F20
What formula do I need in B1. I have tried various of methods of forming the above into a text string and then preceding with INDIRECT but my problem seems to be with the range part.
2. Same as above but I want to the users to write as text (without equal sign) the formula they want to use
View 9 Replies
View Related
Aug 7, 2012
If the total is >= to x but =(I10*0.9),"Over 90% spent",IF((M10+T10+AA10+AH10+AO10+AV10+BC10+BJ10)>=(I10*0.8)&(M10+T10+AA10+AH10+AO10+AV10+BC10+BJ10)
View 3 Replies
View Related
Oct 5, 2009
I have a spreadsheet, but I need the following formula to make our lives easier here.
I have the following columns:
Rate, I/T/P/S, FT/PT
I need a formula that when I enter "I" for Infant it will give me the FT (full time) or PT (part time) rate. All the columns have drop down menus, so if I choose "I" under the ITPS column and then choose FT, I want the formula to be able to give me the full time rate which is 157.94
View 9 Replies
View Related
Jan 14, 2009
I have a new sale structure to put in place the commission is paid in the following way:
below 1500 zero commission
between 1501 and 3000, commission at 16%
between 3001 and 8000, commission at 23%
above 8001, commission paid at 30%
Ergo if you generate 5000 you would be paid 700 ie nothing for the first 1500, 16% of the second 1500 and 23% of the remaining 2000. ( I hope my maths is correct! )
I have tried to manipulate other solutions using sumproduct but my knowledge is poor, the formula I have tried manipulating is =SUMPRODUCT( (A2 > {0,1500,3000,8000}) * (A2 - {0,1500,3000,8000}) * {0,0.16,0.23,0.3}). I prefer single line formula rather than lookups as staff will not be able to see commission rates easily.
View 4 Replies
View Related
Aug 28, 2009
I have a fairly complicated working time calculation I need to make, the sheet calculates how much 'actual working time' is spent on a product. 'Actual working time' means just that, breaks & any other non working time removed from the total.
My sheet set up is as follows:
Column Z = Start Date
Column AA = Start Time
Column AB = Finish Date
Column AC = Finish Time
Column AD = Actual working Time (hh:mm)
Column AE = Planned stop time (hh:mm)
Standard working times are:
Monday to Thursday 07:30 to 16:15
Friday 0730 to 1200
First tea break is 10:00 until 10:10
Lunch is 12:30 to 13:00 (except friday)
Last tea break is 14:40 to 14:50
1) Jobs can be 'in production' for many days, sometimes weeks
2) Overtime may be run before or after shift, also weekends
3) We can have 'Planned stop time' which will also need subtracting
View 9 Replies
View Related
Feb 12, 2014
I need a formula to give me a MIN or MAX value only if there is a number greater than 0 in the cell AND not alphabetical letters (as some cells do contain). I need it to return the value for that cell across about ten sheets--I have that part figured out (I think). But I keep getting a #Ref error.
View 9 Replies
View Related
Jan 19, 2009
Column P is the percentage of body weight lost...
P
-1.98%
-2.92%
-3.87%
-3.48%
0.00%
-0.10%
0.00%
-3.56%
-3.57%
What I need is a formula to look at all 9 data points and give the place number. Remember that the lowest number is actually in first place.
View 2 Replies
View Related
May 5, 2014
I have multiple sheets in excel file. Every sheet having same information for different areas. e.g. The bug was reported. One sheet might have answer in YES and other sheet might have No. I need to get answer YES if even one of sheet contains YES for the field.
View 9 Replies
View Related
Jun 12, 2014
I'm looking for a formula to do the following:
I'm building a table (see attached) where I could have a value in cells D6 to P6 that ranges from 0 upwards. In the respective cell beneath each 'value' cell (D7 to P7) I need a formula that will return a figure of 10 if the value in the cell above is 0.4 or below reducing to 0 if the value in the cell above is 1.0 or above.
View 1 Replies
View Related
