I'm trying to determine the average of a set of numbers, based on a scale of 1 through 10. There are two columns and it would look something like this:
1 3,452
2 2,990
3 564
4
etc.
I want to determine the STDEV here as well but assume I can just exchange the AVG function with that function, once I know how to tie column B (the numbers) to column A (the rank).
Need to write a formula that would assign a letter grade (in row W) to the average in row V, based on the school's scale. So, row V contains my averages (in % form) and I want row W to have the letter equivalents.
93+=A
85-92=B
75-84=C
67-74=D
<66=F
how to get the min value scale value off a graph when .MinimumScaleIsAuto = True?
The reason I ask is that I want to make my baseline for the graph that min number so that names from the x axis are not sitting in the middle of the graph. Incognito Auto Merged Post;I feel real bright...just playing around I found my answer...
With .Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlCustom
.CrossesAt = .MinimumScale
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
this formula and have tried various nested ifs and I can come up with a formula that works. What I am trying to do it to is detirmine what my multiplier should be based on a range of numbers. Let me try and explain lets say A1 is a number and B1 is a number and I need the Formula for C1.
If B1 is between 0 and 5 multiply A1 by 2 if B1 is between 5 and 10 multiply A1 by 4. and so on and so on. Is there an easy formula for this? I know it might be trivial but im stumped for some reason.
I have the following grade scale
F = 0%, D = 60%, D+ = 65%, C = 70%, C+ = 75%, B = 80%, B+ = 85%, A = 90%, A+ = 95%
and I have these students total marks
84%
70%
72%
78%
81%
71%
76%
72%
74%
77%
72%
I want the student who took 84% which is the best to take A+
On other way, I want these marks to be on average instead of the standard scale
I have three columns of data, one containing numerical values. I want to average the numbers based on the other two columns of data. Attached is an example file with the desired output I'd like. What is a formula that will do this?
AverageExample.xlsx
I have a need to work out a scaled trend 1 - 10
for example if I have the following data set:
Sales
Jul-12
Aug-12
Sep-12
Oct-12
Nov-12
Dec-12
Product A
37
28
9
10
3
3
[code]....
Here is my formula that does not return accurate results:
=IFERROR(SUM(SUMIFS(Tenure!$H:$H,Tenure!$E:$E,{"=1100","=1090"},Tenure!$C:$C,{"=12","=14","=13"},Tenure!$G:$G,"=1/1/2013",""})/SUM(COUNTIFS(Tenure!$E:$E,{"=1100","=1090"},Tenure!$C:$C,{"=12","=14","=13"},Tenure!$G:$G,"=1/1/2013",""}))),0)
I need to average column H from the sheet Tenure IF column E is 1100 or 1090 (formatted as text) and IF column C is equal to 12, 13, or 14 and IF column G is less than or equal to a date and column I if greater than or equal to a date OR if this column is blank.
I tried AVERAGEIFS, the above route and many others. For whatever reason, I have been unsuccessful, but close many times.
I'm trying to sort column G for any "Approved" or "Denied" outcomes then find the time difference between columns E and F then divide that difference by the number of approved or denied in column G. I've tried a number of combinations of SUMIFS, IFS, COUNTIFS, and Ave formulas but can't come up with one that works. Here's an example of what I'm trying to do:
Sum E-F if G= "Approved" or "Denied" / the number of "Approved" or "Denied" outc
How can we convert time on a scale of 100 to a scale of 60?
View 3 Replies View RelatedE11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).
E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.
I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.
I'm looking for a solution that could automatically return a value from a predefined named range depending on a given value. eg. I have 3 discount groups:
1) Discount A
Above quantity 5 = 3%
Above quantity 10 = 4%
Above quantity 15 = 5%
Else = 0%
2) Discount B
Above quantity 30 = 2%
Above quantity 60 = 9%
Else = 0%
3) Discount C
Above quantity 15 = 5%
Else = 0%
I have assigned all products to the various groups using vlookup. All discountgroups are named ranges (respectively DISCOUNT A, DISCOUNT B, ...)
ALL OF THE ABOVE IS DONE. How can I make excel look into a named range and return the correct percentage depending on the quantity that is written elsewhere (Keep in mind, most discount groups have a different amount of applied scales as you can see above). So far I can make it look into the correct named range and return a value when a quantity is exactly the same as the minimum quantity. The 'between' or scale functionality ... I have no idea how to work that out within a named range, whilest keep on looking for next scale if condition is not met.
I am trying to set up a spreadsheet where all the sales manager will have to do is input the number of cars sold to determine what the total amount of commission will be for a salesperson. I have something set up so far, but need the spread sheet to do an if/then type thing where it will search the table, and produce the dollar amount for the number of cars sold based on the scale and the number in the # cars sold cell.
I've tried conditional sum, IF, goal seek, and all kinds of stuff to no avail. The spreadsheet is attached.
Is there a way to automatically determine the final row number showing after a manual filter operation? Or maybe getting a list of the row numbers showing on the screen.
The project requires a macro that loops through only the rows of a filtered list, not all of the rows in the worksheet.
I have list of dollar amounts given to me and a general ledger sum which some of those in the list must equal. How can I determine which of these amounts will equal a given ledger total?
View 12 Replies View RelatedI have a spreadsheet wtih a number of rows that contain answers to different criertia in each column.
The row may therefore have cells that either have numbers or letters.
Column A will be the title of the row.
I want to write a function to find the average value of the numbers in any row with title 'x'.
So it would look down column A, and look for those called 'x', and then average all the numbers across all those rows.
I have tried to use =averageif, but I think the fact there are letters in the cells being assessed (which I just want to be ignored) creates an error.
I have an array with an ubound of 10,000 because I will be loading a different amount of numbers each time I run the macro. Is there a way to tell the amount of digits filled inside the array if it is less than 10,000?
View 4 Replies View RelatedIs there anyway to use Excel to determine which of a set of numbers equals a total? I encounter this issue when dealing with accounts receivable. We receive payments with no details on what is being paid. I have a list of open items and a total being paid. I would like to have Excel determine which combination of invoices can be added together to match the payment.
View 4 Replies View RelatedI have a table with this sample data:
A Report Number
B Funding Code
C Approver Code
[Code]....
For cells with column F="NEXT" and matching values in column A (i.e. Rows A2-A5 all match) and "Yes" in any of those same rows (i.e. E2-E5) for column E, column G should read "No". If no "YES"s are in those rows (i.e. E2-E5) then column G should read "Yes".
I can't figure out using VLOOKUP or INDEX and MATCH how to make this work. Would I need to get into VB to do this?
I am attempting to create a template to input specific dates. If a student submits an assignment on the due date, I have set up the spreadsheet to conditionally format whether the assignment was late or not. In addition, I have a column for when feedback is to be submitted and whether it was on time or not. My problem is this...
I cannot figure out a formula or a way to determine:
If the student submits the assignment late, when will the feedback be due? For example, if submitted one day late, the instructor has an extra day to submit feedback.Two days late is an extra two days to work on it etc..
Field 1 input: Beginning Date (e.g 7/17/09)
Field 2 input: End Date (e.g. 7/23/09)
Need: Average costs on and between these given dates
From table with this information:
Date Market two
7/16/2009 80
7/17/2009 80
7/20/2009 82
7/21/2009 84
7/22/2009 82
7/23/2009 82
7/24/2009 82
7/27/2009 82
I need Excel to average Market two 7/17 to 7/23 (80, 82, 84, 82, 82) based on the dates I give it.
If I change the date to 7/20 to 7/24, I then need excel to average (82, 84, 82, 82, 82)
How can I get Excel to average based on different dates? Or basically, how do I get it to pull rows between 2 inputs?
I m making a worksheet for our shooting results. I need the average of the
top 3 results from collum E6 to W6 and top 3 reults from Y6 to AJ6. Can
I am trying to determine for each account number in column A, the most popular method for accessing our services based on visits in column B. And then having those numbers summarized as a total for each method.
See below for an example. As you can see Mobile was the most popular for 2 clients Java was for 1 and Desktop was not for anyone. Not all accounts will use all methods.
Account
Visits
Method
1
10
Mobile
[Code] .......
I am trying to figure out how to take the average of the last 4 "non-zero" numbers in a series of values.
The sheet looks something like this, numbers are all in one row:
7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32 , 7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32
I'm looking for a formula that will look at all the last few totals (starting from the end) and take an average of the last 4 totals that were > 0. In this case it would average {32, 32, 28, 32} ignoring the 0 total.
=AVERAGE(INDEX(E5:IV5,LARGE(IF(E5:IV5<>"",COLUMN(E5:IV5)-COLUMN(E5)+1),5)):INDEX(E5:IV5,MATCH(9.99999999999999E+307,E5:IV5)))
I have this formula where it averages the last five numbers in a collumn, but I want to average the last 5 numbers less the max and min number.
I have cells O1 to O300 numbers in random sequences of no more than 10
I need to average the 5 highest numbers in each sequence eg:
O P
1 2 16 (result)
2 20
3 4
4 6
5 14
6 12
7 8
8 10
9 16
10 18
11 blank cell
12 15 9 (result)
13 3
14 6
15 12
16 9
17 blank cell
18 blank cell
19 4 23 (result)
20 16
21 20
22 40
23 8
24 11
25 28
26 blank cell
down to 300 rows, the sequence of numbers can be from 1 to 10 but never
more than 10
I got a function : =IF(COUNT(O1:O10>=10,AVERAGE(LARGE(O1:O10,{1,2,3,4,5})),"
") but that works only for 10 numbers and not for less
Hello, I have a column with 100 numbers in it. I want to be able to choose a number N and then calculate the average, minimum and maximum of the first N numbers in the list.
I have played around with countif and sumproduct but can't figure it. I don't know how index works, and wonder if that is the key.
My worksheet contains values in D2:AA150 Some cells are blank Is the a formula that will average the lowest 10 values ineach row?
View 6 Replies View RelatedI want to pick the top five numbers out of a random list of numbers and get its average. What is the best way to go about doing this?
View 2 Replies View RelatedI need some different columns within a row to calculate based on a statement found in an earlier column in the row. something similar to below. there will only be two statements total which are noted on the table below.
A
B
C
D
E
1
0
0
20
5%
[Code].....
So basically If column A = disposition calculate D and E in column B but if Column A = Receipt calculate in column C
The formula for D and E is not exactly as shown just using that as a quick example. I'm using office 2010.
