Is it possible to create a formula that would give the sum of cells that are in arithmetic progression in excel?
Example:
Let's first choose 4 cells that are in arithmetic progression, B14 , B20 , B26 and B32 for instance(the common difference here is 6). So what I want to do is: I want to type a formula in another cell, lets suppose C5, that will automatically give me the sum of the values of B14,B20,B26 and B32. I am aware that I can just type on C5 =B14+B20+B26+B32 but and if I wanted the sum of 90 cells? Wouldn't it be too much work to type all the cells? Does Anyone know a formula for it?
I have a range A1:A20 where entering values. I need a function to go true the range and multiple in geometric progression all numbers, if any. ( number of cells with value may range)
These 4 numbers will be part of a monthly report (so I only need the numbers graphed once) and also a yearly report (so I would need to find a way to graph all 12 months).
I want to be able to measure the progression of a cell that is in a dropdown in Excel. Once the cell "closes" it is important but it is more important to see what the cell was before it was coded as closed. Is there a way to see what the last item in that cell was? maybe put it in another cell?
I have a cell (G11) whose format is [h]:mm to store hours worked in a week. I need to use that in a VBA function. If I query G11.value I get a non-integer number (I DO know that Excel stores time internall like that). How can I get in a VBA procedure exactly what is see in the cell..e.g if someone worked 35:15 hours, I want to be able to get 35:15 in the procedure. I need to strip it from there to work out payment, eg (hourly rate * 35) + (hourly rate * (15/60)/100).
I have tried using format but it does not like the "[h].mm" argument.
I'm trying to put a simple spreadsheet together that will help my son practice his arithmetic for primary school.
What I have put together is something which will allow him to change say his multiplication tables fairly easily by changing one number.
Conditional formatting shows green if correct and red if wrong - all very easy.
However I think it would be good to show the selected problem in a top down layout so he can visually see what he is trying to do rather than read across the page.
4 x 2 == 8
rather than 4 x 2 = 8
What I would like to do is change this displayed problem when selects the answer cell he is working on.
I will attach what I have done so far with a display example on the multiplication tab.
is it possible, in a function, to do something like this?
Lets say I'm in cell N7, and here's its formula:
IF (N6=$A$13,(A+1)$13,0)
-if the cell before it (N6) is equal to the value of $A$13, then I want N7 to equal the value of B13. If N6 is not equal to $A$13, then place a 0 in cell N7.
basically, I would like to tell my function to move to the next column if it meets a condition in an "IF" statement. Such as, go from column A to Column B given a certain condition. Otherwise, stay in column A until that condition is met.
I have a timesheet in excel which details the hours worked per person. It is worked out by have time started in one cell, and time finished in the next cell. (24 hour clock).
The timesheet is for a night club, so people start late, and finish early. Therfore, in the total column I have the following formula...
Time arithmetic, I have two cells representing a time range.
The first one (say: X1) is formatted using the custom format [h]:mm and contains a certain number of hours and minutes. It gets its value by summing up other cells in the same format. A typical entry could be 98:35 to represent a duration 98 hours and 35 minutes.
The second cell (say: X2) is formatted as a number with 4 decimal places after the comma, and similarily gets its value by summing up other cells in the same format. It also represents a time duration as a number of hours. A typical entry could be 202.7500 to represent a duration of 202 hours and 45 minutes (because 0.75 of an hour is 45 minutes).
I would like to calculate the hour difference between these cells, and display it as hours and minutes. In the example given, the result should be negative, i.e. -104:10.
My first approach was to use the formula X1-X2 and format the result as [h]:mm, but this gives me a #VALUE! error.
Can I use dates as argument in Boolean arithmetic? I have a list of name with their date of birth and I would like to tell who is between 18 and 25. It's easy enough with number but with dates? Excel 2007
I'm trying to come up with a MapsCo grid to "overlay" geoLoc data. Given the coordinates of a single box within a MapsCo page, I'll can figure out the others once I know how to "from this point, add .5 miles due North and mark another point; from that point, add .5 miles due East and record the next point; etc".
I have a workbook that uses the values that a user had entered into 3 cells to calculate multiple other charts/diagrams on multiple sheets within the workbook. Each sheet would show what the user had entered in the 3 cells to allow them to see what is being used to calculate each table. Is it possible to link these cells so that the user can change the 3 values without having to go back to where he originally entered the 3 values?
For example, a user has entered in 3 values in Sheet 1. A formula in Sheet 2 displays what is entered by the user and uses these calls in Sheet 2 for calculations. When the user wants to change the three values, he would have to navigate to Sheet 1 and enter in the new values to have the workbook recalculate all the tables. Is there a way to link the three cells from Sheet 1 and Sheet 2 so when the user is on Sheet 2, he has the opportunity to change the values on the current Sheet without having to navigate to Sheet 1 to do so?
I wish to copy a merged cell (3 cells) based on if only 1 of 3 cells to the right contain "X". if the top cell does not contain "X" than the merged cell is not copied. Also, is therea more elegant to copy 3 columns at a time rather than do one at a time as my code shows:
Sub CopyICUCAPU() ' ' CopyICUCAPU Macro ' Dim i As Integer
This is a project plan with tasks and dates. Column A is the activity number. (Example 1, 2, 3" etc). Column B is the task (Ex. "Complete Report"). Column C is number of days required to complete the task. Column D is the dependency column. (Ex. Cell D2 =1 in other words Task 2 is dependent on task 1). Column E is the date.
I would like to have a seperate start date cell and a go live date cell.
The objective is to enter a start date, and have each column E date increase based on the number of days entered in Column C. If a task is dependent on another and I change the number of days in Column C I need the dependent task to change the same amount of days.
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
I am using code to filter my 4 sheets Greater then 0 (zero)
After apply above filter now i need to copy multiple rows and paste on another specific workbook for paste i m using below code:
for 1st sheet with the name ("V2")
for 2nd sheet with the name("LV")
For 3rd sheet with the name ("F2")
and 4th sheet with the name("L2")
If I play above code one by one all is going very well,,,,,,or if use in this way all is going very well
But here is a big problem..........if any sheet have no value greater then 0(zero)....then code paste all data... e.g shssts("LV") .Range("C5:C54").Copy but C5:C54 have no data greater then 0(zero) and it will paste on another sheet c5:c54 and again new sheets data will paste below the c54 while c5:c54 have no data.
So I want if any sheet have no data with range is greater then 0(Zero) then skip the copy paste code or use like SpecialCells(xlCellTypeVisible) .
1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.
2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)
3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .
Working in Excel 2007. I am using excel for a data log (basically) and want it to format all empty cells in a row yellow if there is data in column A
Basically, If i have a value in A2, I want any empty cell between B2-G2 to be filled in yellow (as an idicator to the inputter that the cell needs to be completed).
there is already conditional formatting on these cells, which i want to maintain for the non-empty cells. I also have "0" as a value, so I couldn't use the basic conditional formatting setting it =0, it highlighted cells with $0.00, which i do not want.
I am trying to conditionally format the top middle and bottom thirds of a range of data. Problem is, that the range needs to be flexible as sometimes there may be a maximum of 36 cells with data, but sometimes there may be less (so there are blank cells in the range that need not be counted). The methods I have tried always include the blank cells, and so it is not equally formatting the thirds (as it includes the blanks cells as part of the bottom data)....
Here are the 2 methods Ive tried so far using excel 2003) Top 34%: =IF(INT(COUNT($D$3:$D$38)*34%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D $38)*34%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D $38)*67%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D $38)*100%)),MAX( $D$3:$D$38))
I am using Excel 2010 and basically i am trying to fill a range of cell with a green color if any value was enter in a specific cells. Example: I would like to fill range: A10:c13 with a green color (regardless of the cells content in this range) if a value was entered in cell C10 or C11 or C12 or C13.
I've tried conditional formatting but unfortunately I'll have to apply formatting for every cell and for a range of over hundred cells is not efficient.
I have a spreadsheet where I can change the colour of a cell by clicking the mouse, I also have text in many of the cells.
What I need to do is protect (lock) the text so that no one can change the text in any of the cells, but I still want to be able to change the colour of the cells by clicking the mouse in that cell.
I have data on 400 rows. Each row has a maximum of 10 cells with data, but many have empty cells with no data. I would like to sort each row to show values of cells in sequence and eliminate empty cells. I can use the sort row function but its a long process for 400 individual rows. Is there an easier way?
I have a column of numbers and want to make sure everything has been entered correctly from our scanning software. Basically, I want to automatically highlight any cell that has any letter in it (e.g. z12o2 instead of 21202 or R705 instead of 5705), ignoring any cells that contain only numbers. I haven't had any luck using conditions based on formulas like =ISTEXT.
I have spreadsheet with different 100s of columns of dates with 600 rows. The first row identifies which zone the data belongs to (North, South, East, West. NE, SW, SW1, etc...)
I want to write a formula to check how many dates in each column fall in 2015 or later years; This can be accomplished by writing a countifs formula.
Where it gets complicated is once i filter on the Zones;
I want the formula to give me the desired result - count of all CELLS where the year is 2015 or greater - WITH FILTERS ON.
I stumbled upon following sumproduct formula that gives count for visible cells, however when i apply the date criteria, i get incorrect result -
