how can i get excel to read column c and return the answer into column f i have scores in column c that look like eg. 2-2 what i need excel to do is count the goals
in the example obviously 4 and then in column f if its over 2.5 return the value over but if its under 2.5 it returns under is this possible please given the format of column c?
Is there a function, or how would I write a vba to figure out the following.
I have 86 items all with a different price, which come to a total of $348,359.33, is there a way to figure out which combination of the 86 entries will give me a total of $57,673.36
Is 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 am trying to resize a picture on a worksheet to fit a dynamic measurement(the size of the page is different depending on the contents in cells).
I am doing this all in VBA.
so what I have come up with is that I need to
1) determine the total column width of only the non-hidden(visible) cells.
2) resize my picture to those measurements, so I can get an exact fit, and it doesn't create a new page break, by going over.
I have found some code on forums, and am thinking of something like this as a solution(although I don't know how to complete it):
For summing column widths:
I need to get a percentage, whether it is negative or positive or 100% of goal for column E (for ie. Chardonnay would be +168%; CS would be -61%). Everything I have tried from previous posts just brings me to a % sign in column E with no number.
Shipment Goals thru January
%
Actual Shipments YTD
(+/-) Percent of Goal(Col E)
Cases Needed to Order
Chardonnay
0
100%
168
[code]....
I have goal table on table A and i want it data fill to tabel B with constant sum. maybe like sudoku in sum right and sum down.
View 1 Replies View RelatedInstead of having the goals and objectives already in the form I would like to have buttons that would allow my clinicians to add as many goals and objectives as they'd like to the attached treatment plan. I'd like them to be able to click a delete button as well when they review the plan and need to delete a goal that's been met. When my team clicks into the second worksheet they would see rows 10-16 (below). They would then type a STG 1 and an Obj A. They then would click the Add Obj button and add as many objectives that they needed. Then when they clicked the add goal button a blank copy of rows 10-16 (including the Add Obj button) would pasted one row down and would update to read STG 2. Neither of these buttons would appear when the document printed.
Here is a screen scrape of what I'd like the attached form to look like before goals and objectives are added.
add goal.jpg
The original form is saved in an .xltm format but I saved it in an .xlsm format so I could upload it here.
HCT ITP (2-25-14) Abbr.xlsm
I found a thread on this forum that came close to answering my question but my attempts to pull out the coding that I needed was unsuccessful. Here's a link to the thread: [URL] ........
I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.
Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.
I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.
This may not be the best way to do this, but I don't know Macros or Pivot Tables.
I am looking for a way with formulas to do the following:
Within a workbook the 1st sheet is the data entry.
In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:
Example:
Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry.
G2:G2999 in the same sheet is where the data is.
Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?
I need to set up an easy to use spread sheet for my office. It needs to be able to calculate the running total spent of fuel, as well as include any discounts we get and then calculate our total savings.So basically, total spent and total saved.
View 3 Replies View RelatedI'm sure this can be done but I don't think I have the formula correct. What I am trying to do is add each day's total while displaying a positive number if the total exceeds 6000.
View 5 Replies View Relatednumber for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.
View 2 Replies View RelatedI am trying to create a very basic workbook that has 2 worksheets. one is a daily input for tonnes, that then just gets cut and pasted to a different program, and the other worksheet is the running total. i.e., it adds up every time you update it.
been trying to figure out a macro so when you press the update button it then just updates the monthly total.
Colac Production.xls
In an excel work shift schedule, I am trying to total the total number of days someone is scheduled for different shifts. I can get a total for the current schedule but I want to be able to have a year to date total as each new schedule is added in. These totals are in the AF through BC columns in the xray shift totals in the attached worksheet.
I don't know if it is possible or not and am working on a pre-existing worksheet.
Sub sumbotton()
Dim ar As Range
Dim rng As Range
Set rng = Selection.CurrentRegion
Set rng = rng.Resize(rng.Rows.Count + 1)
rng.Rows(rng.Rows.Count).Select
For Each ar In rng.Areas
ar.Resize(1).Offset(ar.Rows.Count) = "=SUM(" & ar.Address & ")"
Next ar
End Sub
I have a sheet in my workbook with at least 180 small tables, there may be more.
I woulds like to be able to change total formulas for all tables at once to show either year-to- date or total year.
For example:
If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.
I have a column with Cells that will sat True or False, the amount of rows will be different every time, I need to work out what the total percentage of True cells compared to the total number of cells. How would this be achieved.
View 9 Replies View Relatedi need to have this information entered in the row below each time which may or maynot be empty. i need a message box with error handling to ask if the user wants to enter more products or finish and another message box if the entries (which start at cell B4) go past B10 that they have to exit and start a new sheet. I have put the code below and attached the workbook so it may be of use to others
Option Explicit
Sub Locate(Name As String, Data As Range)
Dim rngFind As Range
Dim strFirstFind As String
With Data
Set rngFind = .Find(Name, LookIn:=xlValues, lookat:=xlPart)
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address
Do
If rngFind.Row > 1 Then
ListBox1.AddItem rngFind.Value
End If....................
Right now in my code I'm using the below section to determine the column in which to search for dupes, and the last column of data for that range. However, I make the user select only part or one column through IF statements above, so there's no need for the user to input this information. I'd like to replace the choice part with and automated routine that determines the DupeCol from the column section selected, and determines the rowend from the last used cell to the right in the rows in that column selection. But I don't know how to do that and keep the values as strings.
VB:
Dim RowEnd As String, DupeCol As String
Dim LastRow As Long
DupeCol = InputBox("Type in the letter of the column you want to look for duplicates in.", "Fill in the Info")
RowEnd = InputBox("Type in the letter of the last column you want colored.", "Fill in the Info")
I have a big serie of charts (parabolas - open down) that i need to find the max value.
View 8 Replies View RelatedI would like to choose an option (C, Q, T) and get a result in 1 column.
In other words, if I type C in H6, I must get a different result in I6 as opposed to if I choose Q or T in the same column. However if I leave it blank I should have nothing or a 0 in I6.
In the attachment above you will see that I gave a sum for C (A3), Q (A4) and T (A5). The sum for each one is broken down into a percentage rate and a flat rate.
These rates are dependent on the initial amount entered into G6. C, Q, T stand for Cash, Cheques, Transfers. The form of transaction determines the % and flate rate to be charged.
I have a spreadsheet with Ethnicity (White, African American, hispanic,
Asian, other) in column A and Time (hh:mm:ss) in column B.
I trying to determine by how many White, for example, have spent less than 5
hours, >5 hrs but less than 10 hr, etc.
My plan is to make a chart of the data.
I've been looking at sumproduct and COUNTIF but I can't quite figure it out.
I'm working with box quantities and would like to get a formula that would take the box quantity and return the largest value based on some parameters. Here are my parameters:
Box Quantity
- 375 pieces
Break down options
2 - 187.5 pieces
3 - 125 pieces
4 - 93.75 pieces
5 - 75 pieces
Obviously options 2 and 4 are no good as I can only work with even break downs. This leaves me with either 3 or 4 and because I want the largest, I would choose number 3. What I need is help on a formula that will take the value of the box quantity and return the largest whole number. I think for the time being I would like to leave the break down between 2 and 5 as well.
What do I do if I want to determine whether an input is within a specified range, and then for there to be an output of a certain value from another cell?
For example, with tax brackets: if income (D) falls within B and C, return value A(from a separate column)?
Here is the data I have:
Column A
"Formula referencing D1"
"Formula referencing D1"
"Formula referencing D1"
"Formula referencing D1"
"Formula referencing D1"
[code]....
Column D, cell D1 is where you would input a number (income) Column E, cell E1 is where I want the output value.
Basically, what I would like is a formula to put into column E that will do two things. First, to determine if the value in D1 is within either (B1-C1, B2-C2, B3-C3, B4-C4, or B5-C5). Second, to then output the value of either A1, A2, A3, A4, or A5, for each respective range.
I want to write a function that goes out and reads a cell, waits a few seconds, then reads the cell again, since it is constantly changing. Then report back the difference between the two readings of the same cell. The following code does not work since t0 and t1 always come back the same value.
Code:
Function ReadTime(t)
t0 = t
DoEvents
Application.wait Now() + TimeValue("00:00:15")
t1 = t
ReadTime = t1 - t0
End Function
formula I can use to determine age between date of birth (in one cell) and an event date (in another cell)., i.e. birthdate 3/15/2000 with an event date of 3/2/2007.
View 9 Replies View RelatedI am having a bit of amnesia right now, but I know there are some smart cookies out there who can do this in a second or two.
I have a certain range in a worksheet (D6:D27). I need to find the first blank row, or the last row with data +1. I would like a worksheet function of some sort that might be used like this
=LastRow(D6:D27)
The result would be row 10 (the first blank row).
I have been searching for the past hour and just can not get anything to work right. I have tried many different suggestions, but I still draw a blank.
i have this code behind a command button in a userform
Private Sub cmdCalculate_Click()
If Me.txtLength.Value > "6.021" Then
lbM2.Caption = Val(txtHeight) * Val(txtLength)
End If
End Sub
the calculations work fine up till i enter anything over 9.999 in the textbox called "txtLength" Can any one see why this will not perform calculations when the entry is more than 9.999 in text box "txtLength"? i tried replacing the label control with a textbox!
Determine If A Number Is Even Or Odd. i would like to Determine If A Number Is Even Or Odd...?
View 3 Replies View RelatedI want to easily determine the max value of the stocks I own by only changing the daily value but i cannot bypass the circular reference problem. Example:
Stock|Today's Value| Max Value
A | 1 | 1
B | 3 | 3
Tomorrow i'm going to change today's value and my goal is that the max value remains unaltered if today's value is smaller. like this:
Stock|Today's Value| Max Value
A | 2 | 2
B | 1 | 3