Good, Average, Fair, Poor: Automatically Use One Of The Numbers That I Have Set And Add It To Total

Jul 20, 2006

In creating a chart that calculates home prices I need to be able to just enter "Good", "Fixer", "Remodeled", etc...and have that automatically use one of the numbers that I have set and add it to my total. I have created a box with variables for "Good", "Fixer", "Remodeled", etc...How do I make it so I can put any of those variables into the condition of the house row and make it add the dollar amount specified into the totals box? This is the very simple code that I am using to find the totals so far: =SUM(C5:J5)

Here's what I need:

Also, the plug in values in the first picture WILL CHANGE and I don't want to have to change the formula. So I need something to point to those cells.

Enhancing Conditional Formatting: Write "NO DATA", "HIGH", "GOOD", "MODERATE", "POOR", "BAD" Depending On The Value

Jul 6, 2006

I would like to write a function that enhances conditional formatting capabilities in this way: suppose that cell(4,5) contains a number that can be 0 to 5
in the adiacent cell(4,6) i want to put a function that:

1) write "NO DATA", "HIGH", "GOOD", "MODERATE", "POOR", "BAD" depending on that value
2)Choose color font depending on value
3)Choose color background depending on value

I wrote this piece of

Public Function StatusResponse(AdiacentCell As Range) As String
Dim thisStatus As String
Dim ThisFontColor As Integer
Dim ThisbkColor As Integer
Select Case AdiacentCell.Cells(1, 1)
Case Is = 0
ThisbkColor = 2
ThisFontColor = 1
StatusResponse = "NO DATA"............

Automatically Total The Matching Numbers

Apr 17, 2009

If a red number to the right shows up anywhere on the left black numbers then I want a total in column G. I want it to automatically total the matching numbers. How can I do this?

Average Column Of Cells But Ignore Errors And Return Average Of Numbers That Are There

Jun 14, 2013

E11 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.

Sub Total And Average If

Jul 28, 2014

I am trying to combine sub total if the formula matches a certain criteria.

I would like an average of the data I have previously used an average if formula =averageif(D:D,L4,J:J) but now I would like the answer when I filter the data.

Grouping An Average For One Total

Feb 26, 2008

=SUMPRODUCT(--(\$A\$2:\$A\$12=\$D\$24),--(\$F\$2:\$F\$12=\$B\$25),(\$H\$2:\$H\$12))

This is the current formula I have, however I am working with averages. After the first two arguements are done I want the overall average of columun H.

Poor Performance With Range Names

Aug 24, 2006

why it takes about 100 times longer to set a value using a named range. The code below demonstrates the huge difference between Range("A1") and Range("my_range")

Public Sub testloop()
Dim counter As Long
Dim start As Double
start = Timer()

Application.Calculation = xlCalculationManual
Application. ScreenUpdating = False

For counter = 1 To 34000
Range("my_range") = 9999 ' 101 seconds
'Range("A1") = 9999 ' 1.2 seconds
Next counter

Sum The Total And Find The Average Cost

Oct 1, 2008

I need a formula that will scan column A (Code)total the like items (also) add column B (Qty) if there is a number greater than 1. Then add the price (\$) together and divide by the sum of A&B.

In other words find the average price for the total of each item..

A B C
Code Qty \$
PH06003000 1 1504.8
PH06003000 1 1582.24
PH06003000 1 1606
PH06003000 1 1504.8
PH06003000 2 3009.6
PH06003000 1 1504.8
PH06003000 1 1504.8
PH06003000 1 1504.8
PH06024000 1 2499.2
PH06024000 1 2499.2
PH06024000 1 1896.07
PH06024000 2 3909.66
PH06024000 1 2240.7
PH06024000 1 2259.4
PH06024000 15 30030
PH06024070 1 2039.4
PH06024070 1 1958.66
PH06025670 1 2521.2

Poor Performance Finding/Setting Range

Aug 23, 2006

Im having slow performance reading a CSV file into 34000 sheet named ranges. Currently it takes about 8 minutes to read 34,000 records. After stepping through the code Ive realized there are two bottle necks.

1)finding which sheet that the range name resides see getRangeAddress() and

2) actually setting the value to the named range i.e. Range(ra) = dprecord(1)

The CSV file looks like this: <range name>,<data value>
e.g. DPA_1001,99090

Performanace actually seem to slow as the macro runs.

'This macro will read in the a comma seperated value (CSV) file of datapoints.

Dim sFile As String
Dim currentLine As String
Dim delimit As String
Dim counter As Integer
Dim ra As String
Dim fs As Object
Dim ts As Object
Dim dprecord
Dim oldStatusBar As Boolean
delimit = ","

If Then: Show Total Average By Lane For The Season

Mar 26, 2009

I created a spreadsheet to track my bowling scores by date by lane

Date Lane game 1 game 2 game 3 total

What I would like to do is show my total average by lane for the season. 33 weeks. From there I think I can graph it out.

Calculating Monthly Average Based On Total

Mar 26, 2014

I have a workbook with each month as a tab Jan 2014- dec 2014. I have a totals page that's has total billings( the sum is adding all the totals of each month). The totals for each month are in different cells based on the number of individual invoices I enter for each month. I have entered jan- march invoices. I would like to put in a formula on my totals sheet that gives me a ytd avg without changing it. ie: d4/3 then next month april d4/4.

Pivot Table Grand Total Average

Apr 10, 2014

On a pivot table, I want the grand total to be the average. When I code it, the code changes all the values in the column to an average.

if a person's % for April is 95% & the sum of all the people in the table is 1924%. If I change the grand total to average, the person's april % changes to 19% (which is an average instead of a total.

[Code] .....

Get Median (and Average) Value Of Total Number Of Calls?

Apr 1, 2013

I have a team of 8 people who take calls. I need to get the median (and average) value of the total number of calls for these people for a specified date range, say 1/1/2013 to 1/24/2013.

When i use the following f(x):

{=MEDIAN(IF((Call_Data!\$A\$2:\$A\$13000>=\$E\$8)*(Call_Data!\$A\$2:\$A\$13000

Get And Average Price For Total Quantity Of Jacks?

Nov 23, 2013

I am trying to get and average price for a total quantity of jacks

Material Price row G cells 4 - 56
Material Labor Price row I cells 4 - 56
Jack Quantity row L cells 4 - 9

I had this =SUM(G4:56)+SUM(I4:56)/SUM(L4:9) just wont work?

Poor Export Formatting Leads To Complicated Macro

Jun 12, 2008

I'm going to attach a file I'm working with so you can see my issue direction but here is the background:

- file you see is an excel export from another software. This is as clean as it gets.

- what I am trying to accomplish is take the data for each person listed and movie it to it's own sheet via a macro. Creating the sheet name based on the name of the person. I've completed that task before with some help here, but never with data formatted like this.

- does anyone have any options they can think of? I've tried working with the two cells that make up the page number and basicly the move off that but can't seem to get that to work.

- would anyone suggest any formatting changes (while keeping all data integrity) before trying a move?

Display Grand Total In Pivot Table As Average Rather Than Sum?

Feb 11, 2013

I have a column in my pivot table with values that are formatted as percents. I am trying to make the grand total reflect the average of all values in the column, but it keeps showing a sum of all values.

Example: the values in the % column are 90%, 100%, and 110%. I want it to show 100% (the average), but it is showing 300% (the sum)

Capture Average From Grand Total In Pivot Table

Nov 6, 2013

Capture an average from the Grand Totals in a Pivot Table? If so what are the steps.

Calculating Average Dollar Increase In Sales To Running Total

Jun 7, 2014

I have sales numbers which is in the form of a running total dollar amount and I would like to keep track of the average increase in dollars.

So basiclly if the Jan total is \$100 in column a, the Feb total is \$150 in column b, and the March total is \$200 in column c. I would like a formula that will tell me that the average increase in dollars is \$50.

Finding Multiple Names Within A Range And Calculating Its Total Corresponding Average

Apr 16, 2007

In column A I have a list of 5 Auditors labelled Q1 - Q5, 5 Coolum’s across in column F I enter in their scores as a % e.g. 80%. ...So Q1 - 50%, Q2 - 60%. In column A37-A41 I have Q1-Q5 listed, in Column B37-B41 I need to calculate the average deviation per Auditor eg. If Q1 has 2 entries of 50% and 75% return average value in cell A37 which should be 62.50%. I am trying to calculate the average for each Auditor. find attached example.

Average Numbers In Table Containing Numbers And Letters?

Jan 27, 2012

I 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.

Matching Pairs - Column To Automatically Populate With Any Of 3 Digit Numbers That Share Two Numbers

Mar 12, 2013

This is what I need:

Columns B, C, D & E are all populated with 3 digit numbers.

I would like column F to automatically populate with any of the 3 digit numbers that share two numbers, i.e.

F2 might look like this (using 00 as the pair):

001, 040

F3 might look like this (using 01 as the pair):
701, 051, 110, 001, 120

F4 might look like this (using 12 as the pair):
123, 721, 281, 912, 112, 120

etc...

Automatically Decrease Value To Make Total Equal 100

Mar 30, 2013

I receive a large file every month with 30K+ rows of data that need to be manipulated manually before I can send it off. Within the data are "chunks" of data that are anywhere from 1 to 90 rows each. Each chunk needs to total 100 or less, and while they do for the most part, some end up over 100 due to rounding (usually 100.02 or so). The rows above and below each chunk are blank, and I currently have a calc to add each chunk, and flag it if it's over 100. Then, I have to go through each of the flagged chunks and manually decrease one of the lines to make the total 100.

Is there any way to automate any or all of this procedure?

I have attached a sample file with two chunks of data. The actual files contains 14 more columns to the left that aren't shown.

Sample.xlsx

Insert Columns And Automatically Changing Total Column?

Sep 25, 2013

I have a worksheet with data in columns F,G,H and I. There is a total in column J. (=SUM(F1:I1)

I've written some code that allows a user to insert additional columns. However, I'm finding it difficult to find a way of changing the calculation in the total column automatically when a new column is inserted.

Take Individual Dates And Total Into Summary Page Automatically

Apr 23, 2008

I have multiple tabs for each month (Jan, Feb, etc) for the 2008 year and I have a Summary tab.

For the individual months, I have each calendar day in a column and whether the day was for sick, vacation, etc in the rows. So if someone was sick in April, I would mark the day they were out of the office. And so on...

Well now I need to figure out how I can take those individual dates and total them up in the summary page automatically.

Whats A Good Way Of Learning VBA

Sep 1, 2006

I would like to build on my Excel VBA but not sure where to start. P.s I want to aim towards reporting side of things so make reports look good, copy data from sheets onto another sheet, formatting all done through vba, macros

Find A Good Book On Formulas

Jan 29, 2009

if I could find a good book on formulas, I might be able to raise the bar on my questions.

Formulas To Occupy All Rows. Good Or Bad?

Jul 2, 2008

Cell A1 can have only 0 or 1. Cell B1 can have any numeric value. Cell C1 takes the value of B1 if A1 is 1, otherwise it is 0. So, C1's formula can be either "=A1*B1" or "=If(A1=0,0,B1)". Lets say this formula is continued down to the 65000th row. My question is which formula is faster? The first one or the second one?

Row Numbers For Each Sub Total

Feb 1, 2010

i had a subtotal sheet with more than 5000 rows of data.i applied subtotals for my data.i just want to serial numbers for each of the subtotal.what is the easy way for numbering my rows with sub total.

My subtotals are in column A.i inserted a column before it and want to show the row numbers which contain the subtotals.

Total Top X Of Numbers

Oct 4, 2006

Keeping track of racing point scores and I am trying to figure out a formula that will allow me to take the top 6 out of 8 values and total them. We compete in 8 races but the total points standings at the end of the season takes the best 6 out of 8.

For example

Racer 1 100 200 150 90 300 250 210 95

I am looking for a formula that will give me a total of the HIGHEST 6 scores. I tried the =max but that will only allow you to get the HIGHEST value.

Add Numbers To A Set Total

Aug 13, 2007

I’m creating a spreadsheet (attached) that needs to divide up aisles in a warehouse into sections, so that each section has an equal (or as close as possible) amount of work. The “To, From” columns are aisle ranges and the “Moves” column is the amount of work in that range. I need a macro/formula to add up numbers under “Moves”, but only up to the point where the sum is as close as possible to the number in K2 (variable). It also needs to look at the number under “sections required” (I2) (variable) and then split the aisle ranges into that many sections.