I am setting up a spread sheet to compare an invoice between 2 suppliers. It has formulas throughout and a total column. If no data is entered in cells then total column has "value" in it. How can I eliminate the"value" while still keeping formulas in place. Not all cells will have data in them on every instance. But I still need all formulas to be saved

I have a string of text in cell A2. In cell B2 of my spreadsheet is a formula that calculates a number based on the text string in cell A2.

I want to write a VBA loop that removes a single character from the cell A2 string, then calculate the new value in cell B2. I want this loop to continue until the value in B2 falls below a set value (in this case 60).

My code so far Sub trim_text() Dim mytext As String Dim myanswer As Integer mytext = Range("A2") myanswer = Range("B2") Do While myanswer > 60 mytext = (Right(mytext, Len(mytext) - 1)) Loop End Sub

This obviously does not work. In my excel table I have a formula in cell B2 to calculate "myanswer" will this work, or does that code have to be placed into the VBA code?

I have an excel sheet in which I have 3 columns, Column A is giving me a week number from date entered in Column B. Column A is a merged Cell. There are values in Column C.

I want to be able to sum cells of column C ( not entire Column, select few cells) who show 1 (representing Week number) against them in Column A. I have been trying to sort this out since Thursday and have had no success.

I have sheet where percent change is calculated. All of the data isn't put in at once, so i don't want it to a calculate the percent change until both cells contain a number.

Here is the formula i am using: =IFERROR((D4-B4)/B4,"")

Right now when i fill in the first value it automatically returns a percent change of -100%, i want it to wait until both cells are filled with data.

I have this: =LOOKUP(Budget!J3,B11:B42,E11:E42) RESULT: 5%. what I want: =LOOKUP(Budget!J3,B11:B42,(X3)11:E42) RESULT: 5%. I want excel GET the value inside X3 and and go to the other sheet with this variable. X3 = E

I would like a macro to calculate the number of days for a date if cell has date. For example If cell A has a date cell B should automatically do the calculation for =TODAY()-A1

Cell ACell B 3/21/09Calculation from Macro 3/22/09Calculation from Macro 3/26/09Calculation from Macro

In one cell there is a combination of number and text(i.e) 10 Inches or 100 Nos. In another cell this cell is quoted as reference for calculation. Ex. A1*B1/C1 wherein A1 contains Number, B1 Contains Number whereas C1 contains Alphanumerical as mentioned above. But, the contents of cell C1 should be treated as numerals and taken into account for calculation in the above manner. Can explain how to format the Cell C1?

Basically, I'm trying to have a cell (eg.A1) where i can input a dollar value and when enter is pressed, on another cell (eg.A2) the dollar value gets added, then the original cell (A1) is cleared for the next input.

Then when I put the next input (A1) I want it to add on to this running dollar total (A2) and yet again A1 is cleared for the next input.

In addition to this I require an additional cell (eg.A3) that when each time a value is added in A1 it counts the times this is done.

I have a file wiht 2 sheets, on one sheet I have a macro that opens 25 other excel files and pastes information into the sheet in a specific order. Then on the "main" page I have different calculations based on the information from the other sheet.

The Information is listed in a row so for example B2:M2 or B3:M3. If a value has not been assigned the vcell will be empty (No formula or anything, completely empty)

The Value in the cells will be between 0 and 100 (its a score from 0-100) so in the "main" page I want a formula that calulates the LATEST score (It will always be the first one on the furthest left and the latest one to the furthest right). Is there any easy formula to calculate this?

String Multiples for 3 different criteria in same cell -- "if", "or" "abs" --- not sure if any of these are correct

1) Criteria 1 and 2: In cell "E" I am wanting to minus "B from C", if "A" is greater than or equal to and less than "=>1000 or <15000"

2) and the other criteria would be if "A" is greater than or equal to ">=15000" then minus "b from d" -- answer would also be in the same cell "e".

I hope I am saying my needs correctly. Basically, I have 3 different sales criteria with 3 different bonus offers. One offer is for sales less than 10,000, another offer is for the range 10,000 to 14,999 and the last is for the range 15,000 or more. I am actually linking the values from different worksheets, however I have used generically "A, B, C, D and E".

below are the 3 separate formulas that do work on specific individual rows. However, I really need the criteria for all three variables in the same cell. Right now I am just copy/paste the appropriate formula that fits the specific value in each row.

Is it possible to use a formula to calculate a cell reference within a formula? e.g. is there some way I could reference cell C47 in a formula by saying C(40+A2) where A2 contains the value 7. I often want to sum a certain number of cells in a list, from the first one to the nth one and need to be able to calculate rather than hard-code n.

I have a row of data starting in cell E4 that could, theoretically, go to the far right end of the spreadsheet. I need to enter a formula in cell D4 that calculates the average of every other cell in this row, starting with E4, that is E4,G4,I4,K4...

I need to calculate the amount of time my daughter will be in daycare which varies each week. I have to give the same sheet that I have hidden calculations on to the center. I have to have the time she arrives at 7:15 AM (Cell A1) however they don't start to bill until 7:45 AM (Cell A2) and continues until 11:30 AM (Cell B1) and need C1 to be total hours between A2 and B1

I need a formula that will calculate the hours between 7:45 AM to 11:30 ONLY if cell A1 has a time entered in it. If A1 is blank then it should calculate 0 hours.

Example (WITH something entered in A1) Arrive Time End Billable Time Total Billable Time A1 - 7:15 am to B1 - 11:30 am C1 - 3.75 Hours

Start Billable Time A2 7:45 am

Example (WITHOUT something entered in A1) Arrive Time End Billable Time Total Billable Time A1 - to B1 - 11:30 am C1 - 0.00 Hours

Start Billable Time A2 7:45 am

If it has to be in 24 hour time is there a formula that I can enter in another cell to convert 12 hour time to 24 hour time again ONLY if A1 has something entered in it?

I need a formula which can calculate row 60 for blank cell. If true then apply another condition which checks whether or not the cell above the row 60 up-til row 50 has any bold fonts or not. If true then show True and if false, show False in cell J1.

I have value1, search value1 in a row, if found get cell address and store in cell10. Then value2, search in the same row, if found get cell address and store in cell11. Then calculate number of rows between cells with addresses in stored in cell10 and cell11.

How do store the addresses and how to calculate this number of cells?

I'm after some vb code that will only allow the range - PRODUCT_Selections!A2:C370 to calculate, when something is changed in the range - Input!T11:V500

I would like all other calculations in the workbook to continue as normal.

I'm trying to have a cell (eg.A1) where i can input a dollar value and when enter is pressed, on another cell (eg.A2) the dollar value gets added, then the original cell (A1) is cleared for the next input.

Then when I put the next input (A1) I want it to add on to this running dollar total (A2) and yet again A1 is cleared for the next input.

In addition to this I require an additional cell (eg.A3) that when each time a value is added in A1 it counts the times this is done.

There is a column in my spreadsheet which needs to reflect a " percent done". I used to enter just a numerical value in (eg. 20) to reflect 20% done. My boss wanted it to show as an actual percentage (eg. 20%). I changed the format to Percent, Zero decimal places. Then you had to enter .20 to get the 20% to show. Now my boss doesn't like that - and wants to be able to enter "20" to get the 20% to show. I put the following code in my SelectionChange event...

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'other logic If Target.Column = Asc(PercentDoneColumn) - 64 Then If Target.Value <> "" Then Target.Value = Target.Value / 100 End If End If 'end other logic End Sub

My problem is that this works well when putting a new percentage in, but not for when you click on a cell that already has a percentage in it. For example, if you have .2 (displaying "20%" in the cell), and click on that cell, it divides that number by 100, and gives you 0.2%, displaying as "0%". I want to enter a number between 0 and 100, have it displayed and stored as a percent. At this point, I'd settle for the displayed, but would prefer to have it stored as a percent, too. I also want it to not reset my value unless I'm putting a new value in!

I am working on an employee weekly schedule and would like to be able to calculate the amount of hours an employee is scheduled each day. For example; if you worked from 7am to 4pm, I want to have a formula that can determine that (7am to 4pm= 9 hours) then sum the total amount of hours for all employees scheduled that day.

Mon Tue Wed Thrs Fri Sat Sun Total Employee 1 7-4 7-4 8-5 off 2-10 5-10 off 40 Employee 2 8-5 11-8 off 7-4 1-8 off 8-5 43

I am running the following code to check if a value = 0 is in specific cells. Because this code on all the cells runs each time any cell in the workbook is calculated, the screen flashes. I would like to stop this screen flashing by having the check done only when the specific cells are exited....

I have a set of values in the 2nd column (Col B). I have shown in Column C (I've added some notes to the right).

mon........7..........(first value, so nothing here) tue........8..........na, or ERR.......................... 8 IS the highest value in B, so no math to do yet wed.......10..........na, or ERR.......................... 10 is greater than 8…the new highest value in Col B thu.........8...........(20.00)............................. The value, 8, is 20% less than the Highest value so far in Col B fri..........6..........(40.00)............................. The value, 6, is 40% less than the Highest value so far in Col B.....

Namely, any value in Col B that is LESS than the highest, greatest, largest value that has preceeded it in chronological order will reflect by what % it is lower. (Eg, 8 is 20% less than 10). When a NEW higher value appears in Col B, there would be no answer in C (or simply "ERROR", or n.a.), and this new, largest value would be what subsequent values in Col B are compared to.

I'm having trouble with the "sister" formula I need to add. I've tried the function SMALL, but apparently not in the right format.... Here is some data again, with the answers I'm seeking & notes in Col C:

mon....6....(first data pt, so nothing here) tue.....8.....33.33 (this is 2/6ths higher than 6) wed...10....66.66 (this is 4/6th higher than 6) thurs...8....33.33 (same as tuesday) fri.......6.....00.00 (Equal to the lowest data pt, ie 6) sat.....5.....(finally a vlaue less than 6, so: "ERR") sun.....7.....40.00 (7 is 2/5th higher than 5) mon....15....200.00 (10/5ths higher than 5) tue.....4......(a new lowest value; "ERR") wed....8......100.00 (4/4ths greater than 4)

That is, any value in Col B that is GREATER than the lowest, SMALLEST value that has preceeded it - in chronological order - will reflect the % by which it is greater. When a new lowest value appears in Col B, a simple text "ERR" would appear, and THAT new low value will be the new "target" the subsequent values in B are compared to. (In the example, once 5 appears, we stop looking at 6...)

I am trying to found or create a formula that will display cell width to the decimal place.

I can use the formula Cell("width"), but it rounds to the nearest integer. I need to display the decimals. For instance, if the actual column width is 8.43, the Cell("Width") formula will display 8. I need it to show 8.43.

I've set up a simple spreadsheet to keep track of my food expenses every month. The first column is for the date, the second is for the daily total expenses and then the next three columns are where I add the data which is then calculated into the daily total column.

I also have a total at the bottom for the entire month. Now what I want to do is I want to also have underneath the grand total, a cell which keeps track of my average daily expenses. Basically I want to divide the total expenses by the number of days which I've entered data. Now normally this would be fine but because I've applied the formula to all the cells in the expense column, it automatically lists every day as "0" rather than leaving it blank. So when it does the average calculation it's dividing my total by 30 days rather than by only the 7 days I have data for.

I am struggling with a formula, that will display a certain cell only if a match is found, else produce a sum. It will be easier to look inside the document rather than explaining it on here. Described as well as I can inside the document

(None of the data used is factual. I created it myself to replicate)

I have an image overlapping a range of cells. I want this code to scan the pixel at the center of the cell, find out what color it is, and set the entire cell to that color, for each cell. Unfortunately with the GetPixel function I have to specify the x and y coordinates in terms of pixels from the top left corner of my monitor. The "CurrentCell.left & CurrentCell.top" method is not giving me the right location (Should start at cell AA1). If you would like, open up the file, go to sheet 2, insert an image, and click "Generate Field from Image". I set the cursor to follow the current position so you can see that it is selecting pixels from the corner of my monitor rather than cell AA1. Is there a way I can calculate the screen position of the top left of cell AA1?

The debugger said: That "Sheet" is not allowed in the subtotal-function. because i would like to calculate two subtotals in two sheets an write the sum in another sheet

The following macro does well at concatenating 2 constant values and the values in columns A and CF and places it in the CP field. What I need to know is is it possible to modify this code so if there is NOT a CF field cell value (that is it is blank) that the formula does not run and moves on to the next row? That is, the CP1 cell remains blank?

I am updating a sports competition ladder. There are two aspects to compiling the table I would like to automate but have been unable to find out how via the excel documentation.

The aspects are.

1. When a team loses by a margin of seven points or less below the total points scored by the winning team then the team is awarded a single "1" bonus point that contributes to their overall competition points tally. 2. When a team scores four tries or more they are likewise awarded a bonus point.

I require formulas that will automatically calculate the bonus points into a cell.

Situation One cell 1 = Points For (eg. 17) Cell 2 = Points Against (eg. 24). In this instance the team did not win. They scored 17 points and the winning team scored 24 points. Therefore the team lost but lost by seven points and is therefore entitled to one (1) bonus point. SOLUTION NEEDED - I need a formula that will calculate whether cell 2 is seven or less than cell 1, and if so automatically place a total of "1" in cell 3.

Situation 2 Cell 1 = Tries Scored (eg. 5) In this instance the team has scored five tries. They have scored more than four tries or more and is therefore entitled to one (1) bonus point. SOLUTION NEEDED - I need a formula that will scan the number in cell 1. If this number is 4 or more, then a total of "1" should be automatically placed in Cell 2.

I have looked into the excel help but to no avail and the search terms are so broad it was a nightmare trying to search the forums.

Is it possible to calculate hours if both the start and end time are in the same cell. Unfortunatly the developers of our scheduling program developed it so when you export the schedule to excel it shows Greg in A1 and 3:00pm-9:00pm in A2. I would like to have excel calculate the 6 hours so I can use it to auto generate another spread sheet I am making.

I would like to be able to round off the decimals resulted in the POV field on my worksheet and have a running total of the entries listed below. This occurs sometimes when the mileage units have decimals. In this example, the POV values display $1.00, 99 cents, and 93 cents. Their resulting total should be 2.92, but Excel calculates them as $2.93 due to the additional decimals.

The problem is that if I apply an =ROUND function to the formula in the POV field, while it will display a correct answer if all the date and mileage fields are filled in for which the formula has been applied, it will not give a running total in instances where there are empty date/mileage fields, reporting "VALUE!". Is there a way to force Excel to display the running total?

I have attached the worksheet, with the example above entered in. I have included the formula I've been trying to fix on my worksheet on the bottom as well.

I want to calculate the total number of characters in a cell or group of cells. Also, as a second step, I want to indicate which cells have a total number of characters greater than a certain value.

I only want the formula to operate when there are values to calculate and then ONLY refer back to the last calculation. If there are gaps between the calculations I would like those to remain blank. Is that possible? I have attached an example of what I am trying to do.

ColorFunction is a great tool, but I would like to use it to calculate the Standard Deviation of color filled cells. Can the following code be modified to do this?

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell,vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function

I'm trying to apply the code for accumulating value in a single cell to a range of cells.

I want to use the code found in this thread [URL] ..... but, it only lets me edit "B1" to another single cell. I need my cells G4 to G469 to operate the same way.

How to edit the code so I can include a range or show me a version of the code where I can enter my desired range?