I've different rating matrix as below;
more than 90% - " Excellent"
80% to 89% - "Very Good"
65% to 79% - "Good"
below 64% - "Low"
hence if any of the rating falls in a cell need a formula to corresponds that (lets say in cell "A1" if the rating is 85% then result should be "Very Good")
I'm trying to get a formula working that is basically for a rating chart. The chart is somewhat simple, but the formula is a bit complex (for me anyway)
So the details are the rating is on three axis with three levels, and here is what I need to do.
At the moment the formula below works, but it isn't 100% accurate because I actually need to take an average of F5+G5 then take that value and add it to H5 then divide by two to get the proper average of the two charts. (if that makes sense.
=IF(AVERAGE(F5:H5)>2.33,"High",IF(AVERAGE(F5:H5)>1.67,"Medium",IF(AVERAGE(F5:H5)<1.67,"Low")))
Here is the formula that I thought would have worked, but Excel begs to differ
=IF(SUM((AVERAGE(F5:G5))+H5)/2>2.33,"High",IF(SUM((AVERAGE(F5:G5))+H5)/2>1.67,"Medium",IF(SUM((AVERAGE(F5:G5))+H5)/2<1.67,"Low")))
I played with the brackets quite a bit thinking I had something in the wrong order, but nothing seems to work. I always get FALSE in the field and an error as well when I enter the formula.
I have a list of employees who each have a performance rating (very top, consistent, etc). Each employee is also either above the midpoint of a salary range or below (which is represented by a separate column with 'Yes' for above and 'No' for below).
What I am trying to do is apply a separate set of %'s for each group of employees. So, for the employees who are below, I want to return 10% and 8% for each respective rating, but for those employees who are above, I want to return 8% and 6%. (for example).
I have some delivery ratings from suppliers, and I want to assign a specific letter to each rating, depending of the obtained number. Criteria is:.........
View 4 Replies View RelatedI am putting together a formula that will be able to choose the calculation. There is a couple of choices with the spreadsheet registry, non registry, and interfile. They have different standards per hour 56, 40 and so on. What I have so far is not working. It is =volume/(time*standard),Volume/(time*standard) with each standard being different to bring the correct percentage. How can I put the two to three formulas in one cell
View 2 Replies View RelatedHow do I enter a formula to find a dollar amount for a different range dollars? For example, if $0 - $1000 = $50 and $1001 -$2000 = $100. In reality, I would like to enter a specific dollar amount within one of the levels (column 1) and the sum (column 2) would auto fill.
$0-$1000
$50
$1001-$2000
$100
Im looking for a ELO rating. Its a system which they often use with Chess.
View 3 Replies View RelatedI have an interest in horse racing and I use spreadsheets to help with my form study. I have reasonble knowledge generally of but am self taught.
What I want to be able to do automatically is to re rate 6 columns (shown with yellow at the top)(columns BA-BF)in a worksheet after scratching occur. I have away of eliminating the ratings out of the 6 columns after scratchings but would like to rerate the columns so the ratings start at 1 and rate up. Now when a zero appears it needs to stay a zero as that means it has no rating but where a blank occurs it would indicate a scratched horse. I want to keep the number of rows without deleting any for the scratched horses so it matches other worksheets I use and also need to keep the horses in race order.Hope this makes sense.
Basically...here's what I'm trying to do...
I have 7 numbers. The numbers are going to be grouped in threes.
(Ex. 123,124,125,126,127,134,....)
I want to assign column/range for each number...
1=A2:A100, 2=B2:B100, 3=C2:C100...
So for 123 the formula would be...
sumproduct(--(A2:A100>criteria),--(B2:B100>criteria),--(C2:C100>criteria))
It would be awesome to have the formula automatically calculated based on the numbers I choose.
I am trying to save some time converting a cut up inventory catalog number. Specifically focusing on the color code. I have the list of colors associated with their specific part number. The order cannot change or my sheet will mismatch.
What I want to do is convert the # in column A to the color code text in column b so that it matches the translated code in column C? For example
1 I want to tell 1 to = Polished Chrome anytime #1 appears in my column.
What formula should I choose to achieve that goal. As well, how do I know what data to imput into the formula wizard?
Sheet is attached.
I have Cut a few thousand items off the bottom of the sheet. I can apply to additional data left off.
I am trying to display result of risk assessment matrix in excel. I have three columns PROBABILITY , IMPACT and RISK RATING (probability *impact).
Probability = Very Low(1), Low(2), Medium (3), High (4), Very High (5)
Impact = Low(1), Medium(2), High(3)
Risk Rating = Low (value between 1 -3), Medium (value between 4 - 6), High (value between 7 - 15)
Is there any way that I just use TEXT in the cell (visible to user) and excel does its magic in the background using the numbers that I have for each TEXT? I don't want to display numbers.
I have a list of issues and depending on their priority would require a different target closure date e.g. low priority = 90 days, medium = 45 days, High = 10 days.
So if I have the following columns:
Issue No (Unique Ref #)
Date Raised
Priority (Low/Medium/High)
Target Closure (based on the pre-determined criteria as detailed in above paragraph)
Can somebody suggest a formula to enter in the "Target Closure" column so it is automated?
I am looking up a risk matrix to return the risk rating... it is very simple, it works on one sheet, but on a different sheet (looking up different data, but same basic format) it returns exactly the opposite rating.
My formula is: =INDEX($G$6:$K$10,MATCH(I19,$G$5:$K$5,0),MATCH(J19,$F$6:$F$10,0))
Here is F4 to K10
I have data validation on I19 and J19 based on the cells below
Consequence
Priority
Very Low
Low
Medium
High
Very High
[Code] ..........
BUT... when I put Very Low and Very Low in the two cells (I19 and K19) I get Very High as the return.
I have created a form where people will take a survey, and the responses from that survey will be carried over to another worksheet that will analyze their results.
For each question in the survey, I want them to choose only 1 of 2 responses. For example:
Place and X next to the statement that describes your opinion most often:
__ I prefer to work with others.
__ I prefer to work alone.
The responder will choose either the first or second response, but I do not know how to limit it so they can't answer both. I want a way to limit the worksheet so if they type an X in the first field, they are unable to type one in the second field, and vice versa.
Is there any way to limit the responders ability so that if they choose to respond to the first statement, they are not able to also respond with the second statement, and vice versa, while not affecting the results page that uses the IF formula =IF(sheet1!A8="X","X","")
Any good formula for the following:
Item
Value
Result
[Code].....
The formula needs to first look at each item, select the max value in each group (there are three in the "1" group and four in the "2" group), then return ONLY one result per group. The problem I am having is only returning one value per group. I can write an array formula that returns 1200 or 1000 in each group in every row, but I need to only return one value.
I downloaded a dashboard template that has two tabs of data and I would like to add additional tabs.
I would have to modify the IF and CHOOSE functions?
Formula view:
Capture.jpg
Here's the pretty view:
Capture2.jpg
how I can make a calendar that pops up when the user mouse clicks or tabs/arrows over to a cell so they can choose a date instead of manually typing in the date?
View 6 Replies View RelatedIf you look at "B9" I have created a list of names and when I choose a name their ot hours appear in "K9". Now when I try to do the same in "H9" to bring back there qualification it gives me an error. I also tried to do the same in "M9" for the phone numbers and it didn't work.
View 7 Replies View RelatedThe choose function only works with separate values, but not with a range.
I'm looking for a formula that returns the value in a range based on a number.
e.g. CHOOSE(5, A1:A10) should return the value of A5.
I can program it as a function, but I expect there is a standard formula for this.
I'm trying to count the number of times "Y" occurs in column H and one of four values occurs in column B. I'm new at writing arrays and what I have so far is:
{=SUM((Main!$H$4:$H$700="Y")*OR(Main$B$4:$B$700="FGZ","FHZ","FLZ","NAV"))}.
This is returning a #VALUE error. A
i have a column of data, and from that list i want to choose the lowest value grater than a reference value.
for example
the list: 1 2 3 4 5 6
the reference: 2
what function should i use to get the lowest value grater than 2?
I have two formula's available for a cell. Its actual value, and a previously forecasted value. The actual value is found using an index formula and the forecasted value is a forecast function. i want to create a macro and link it to a button such that i can change which formula used in the cell accordingly. Is this possible?
View 1 Replies View Related"A1" has a number containing "1000"
I want column "B2" to choose the next number in sequence,(1001) so long as "A2" contains these letters "EHT". if it does not contain "EHT" i dont want excel to insert the number "1001"
But if cell A3 contains, "EHT" I want the next number in sequence to be instered automatically... (1001, 1002, 1003")
how you know which object library to add to references when you want to automate an application?
for example Adobe acrobat.
or internet explorer ( ie).
I know that the .dll for ie id shdocvw (an i know it's explicitly listed under internet controls) but how would i know this is it wasn't listed?
Also, correct me if i'm wrong, but usung the shdocvw.dll will only give you access to the main controls of Ie nd you would need to add a HTML library to do anything use full. How do you know if there are other libraries available can make the 'usefulness' of one library more useful?
I have an Excel file with a column a with names and a column d with numbers. Another Excel file needs the numbers from column d, but only always from the first two alphas, the first two betas, the first two deltas and the first two gammas.
The problem is that the amount of alpha, betas, deltas and gammes vary each month so i cannot choose a specific cell because this will change.
Column AColumn D
Alpha 100
Alpha 200
Alpha 300
Beta 400
Beta 500
Beta 600
Delta 700
Delta 800
Delta 900
Delta 1000
Gamma 1100
Gamma 1200
I have 8 values in a drop-down list that I've created with excel " Validation" function. Each one of these values changes my data in the sheet. I'm trying to set up a macro that will choose each one of these values and print out the sheet when each new value is chosen.
View 3 Replies View RelatedI have = LOOKUP(REPT("z",255),CHOOSE({1,2},"",LOOKUP(C3,{1,2,3},{2,1,0}) )) in cell D3. C3=1 so I expected a result of D3=2. Instead I am getting a blank cell.
View 4 Replies View RelatedI would like a user of my program to choose the destination folder where a .txt file will be saved. I have code below which allows the chooser to browse through folders and choose a file:
Dim f1 As Variant, s1 As Variant
s1 = "#1: Choose the .txt file which contains montly data for the stock price."
f1 = Application _
. GetOpenFilename("TextFiles(*.txt),*.txt", , s1)
txt2.Text = f1
I want the user to browse folders and subfolders like they can with the above code. However the user will select a file path not a particular file. In the case of the code above, the text of Text Box txt2 would be set to the file path (i.e. "C:Documents and SettingsAdministratorDesktop")
I am trying to get the user to choose which disc the file structure should be stored in. Is it posible to get a dialog box which contains all the disc drives? I tried using the GetOpenFileDialog, but it seems I must choose a file. I must only choose Disc Drive.
View 5 Replies View RelatedI have used VBA and can do basic programming, but have no experience with popups beyond msgbox.
I've written a macro that I want to run, but it needs an input. This input is a worksheet name. What I want to do is run the macro, have a popup that has a combo box ask me to select which worksheet I want, either click ok and save that worksheet name to a variable or click cancel and exit the macro.
My Pseudo code looks like this:
Run Macro
Combo Box Popupoption1=sheet1option2=sheet2option3=sheet3If OK ThenSelection = Combo Box choiceElseIf Cancel ThenExit Sub
...Macro Code....