I'm trying to do a CONCATENATE function in Excel 2003. The first part of the function (this is working correctly) is a) concatenating 4 cells from a different worksheet (WB_NEW) into one cell and is b) putting a space in between each entry:
Any way to apply a formula to a cell depending on what choice is made from a drop down box in a cell that it refers to.
I have a drop down box in A1 with 2 options: Cookie Making Costs - or - Cake Making Costs In cells A3 through A10, I have cells with the costs for all the ingredients (and B3 through B10 for cake ingredients). If I select Cookie Making Costs, I would like B1 to apply a formula that sums up all the costs associated with making cookies "=sum(A3:A10)" If I select Cake Making Costs, I would like B1 to apply the sum of the cake ingredients "=sum(B3:B10)"
Is there anyway that when I choose (for example, Jan) the result shows the sum of January numbers. On the other hand, if I choose Feb, the results would be the sum of February. See Mar on cell C8 and Feb on cell E15.
I'm trying to find out a function that allows me to evenly spread the amounts given a certain start year and quarter and end year and quarter. Let's say, I have 20 dollars and the starts in 3Q/2012 and ends in 2Q/2013. Then, the money should be spread out in four quarters. If the end is in 3Q/2013, then the spread is five quarters, and the amount distribution automatically changes.
Im currently studying for an ICT A level and im in my first year doing AS. Im working the AQA board and i have a project that i have a target for an A! I need to create a system for a business and write a report on everything i did...wont go into detail. Anyway. i am having trouble with an IF FUNCTION!!! I have a cell (E35) that contains a percentage.
I want the IF function to display a message depending on what the percentage in cell E35 shows;
IF, 0>> Display "Bad"
40>> Display "OK"
90>> Display "Very Good"
E35>=100 >>>> Display "Excellent"
I have tried so many things and I was so sure i entered it correctly, but it keeps coming up with an error.
I have a small problem using the ‘sumproduct function’ which I am using it to calculate the area under curves using the following equation (=SUMPRODUCT((B10:B109-B9:B108)*(D10:D109+D9:D108))*0.5). However I have hundreds of curves to calculate the area under and the length of the data series for each curve is different, I am currently changing the length of the data series by hand. I have attached an example worksheet of 2 sets of data (the formula I am using at the moment is highlighted in blue – row 203). Is there are function or formula that I can use inside this equation to change the range of the formula depending on the length of the data series?
What I am doing is comparing Zip Codes from 3 Data Bases against each. If all 3 DB's are equal then I need a responce of "Ok", if there is a difference in any of the 3 then I need a "Bad". I know this is simple, but Data Base A can have the Zip Code as all 9 numerals or just the first 4. It only depends on who entered the Zip whether or not the trailing 4 numerals were entered or the were entered as all zeros. To throw another wrench in it, if the leading numeral is a zero, all three DB's will drop the leading zero.
I want to do all of this in 1 Function, not 5 fromulas.
What I need is for Database A, test the cell for Length and if it is 8 or 9 numerals then remove the right 4 to only leave the left 4 or 5 numerals (4 if it had the leading dropped 0) and then do a simple If A=B and A=C Then "Ok" Else "Bad".
I receive montly investment performance projections going out monthly for 10 years. I need to show the return annually for each year. I created a SUMIFS formula to sum the percentages between two date ranges (beginning of the year and the end of the year for each year). However, my SUMIFS formula does not take into considersation compounding. How can I modify the SUMIFS formula I created to take into consideration compounding?
I know how to convert monthly percentages to an annual percentage,using the PRODUCT formula =PRODUCT(1+T15,1+U15,1+V15,1+W15,1+X15,1+Y15,1+Z15,1+AA15,1+AB15,1+AC15,1+AD15)-1, whereas T15:AD15 is the monthly percentage gain. Is there a simplier/alternative way that I can incorporate into the SUMIFS formula?
If it is too complicated to utilize a SUMIFS formula, I would like to be able to select a range of cells and have Excel calculate the result vs typing "1+[select the cell]," for each month. It takes me forever and open to input errors. It would be much easier to be able to select a range of cells when creating the formula.
I'm using a heavy excel file and I have linked some cells in one sheet to other worksheets in the same file. My problem is that when I change the value in some cells they do not change in the other cells linked to the ones I have modified.
I have some formulas in my workbook that use the weeknum function from the analysis toolpak. I have the analysis toolpak installed, yet every time I save the file and reopen it, the formulas with weeknum are gone, with #N/A in its place as the formula. Sometimes the results of the formula from the last time remain (the cells contain the number of the week, but without the underlying formula), sometimes not. All the other formulas are fine. The formulas are simple, like =WEEKNUM(C2)
I have been trying to save as a 97-2003 .xls file. Today I'm trying to save as .xlsx, but I have to rebuild the formulas first, so I don't know how that's going to turn out.
In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.
I am creating a worksheet that I need to encorporate an If statement into. What I need to happen is when cell A1 has a number in it (1,2,3,4...) I need it to perform a function on cells B1 and C1. However there will always be information in cells B1 and C1, but I only need it to perform the function when the number in cell A1 is anything but 0.
How can I create a rule for a table that finds a specified word and replaces it with another such that the rule would continue to apply to the table even when a new record (or row) is added. There has to be a simple way to do this without a macro.
I have 2 input boxes. Input Box 1 asks for user's Date of Birth. Input Box 2 asks for user's name: Pretty simple right! They will loop until following 2 conditions are met: If the user enters something in them OR user presses Cancel. If they are empty and Ok is pressed then keep on looping.
I have set all cells in a worksheet to be protected. I then selected specific cells that are not protected. The problem is that cells A11:D29 except C13:C14 do not follow the protection rules in that I can edit them (but when I right click, I cannot format, just like they're protected). These cells are NOT part of the group that was selected to be unprotected. When I unprotect the sheet and check the protection setting for these oddball cells, it shows as protected. All other protected cells do not allow entry (which is what I want).
how to display the current set of conditional formatting? I don't want to go to the cells I want a listing of all the conditional formatting rules. I am sure I have seen this somewhere in the past but cannot locate it.
I have started to use excel again because I need to create a spreadsheet for maintenance hours done on a vessel. The problem I have right now is, how to set a rule to allow only certain multiple of a particular number to appear. For example,
I have a initial cell value where I have to manually insert the total number of hours that have gone by in the vessel. Let us say that this value is 500 hours.
In another cell which is labeled, "Last Completed", it would have to read at how many hours this job was last completed.
I also have another cell which gives you the time interval before the job is performed again. This value is 250.
So if the current number of hours is 500, the last completed date should be 250. (assuming Maintenance was done at 0 hours and 250 hours).
How do I make it so that the "Last Completed" cell would read 250 until I hit 750 hours which at that point should read 500 Hours.
I'm looking for assistance with working out the following formula. I live on an apartment complex in Marbella Spain and we pay Monthly Community Fees of say $100.
We want to calculate a penalty amount for late payers of say 30% of the monthly fee due for non payers? So for instance if the monthly amount due in Cell D8 = 100 and the actual amount received in cell E8 shows 0 or then increase any figure due for the following month in Cell F8 should increase by 30% ($ 130) in the following month. and so on
I use Excel ver 2003. Trust my query is reasonably clear?
I would like to fill some cells with a customer id if this customer is the same as the cell bellow. As my example below.
CUSTOMER idCUSTOMER NAME TYPE 737346 Nikos 81 1154765 Kyriakos 81
1154780 Thanos 81
I would like to fill automatically the empty cell bellow Kyriakos the customer id 1154765 in new column. I want in column "new" to fill with customer id and when the customer id is empty to fill the same customer id as above customer id.
I am trying to clear row with the following rules:
the last cell must not contain 0, if 0 delete entire row. the first cell the the A row should be only three character long, if more delete entire row.
Now I managed to delete the 0 in the last cell and but cannot get the VBA to count the characters in the first row and if more than three, delete the entire row . I know to count the characters in a cell is done by Len(Rng.Value) , but getting the VBA right is alluding me .
the script is here,
Sub Del_rows_with_zero_in_column_of_activecell() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Const StartRow As Long = 1 'Row to Start looking at Dim StopRow As Long Dim Col As Long
I have found this very challenging, and have really confused my self on how to systematically approach this. I have the user enter the value in the black highlight boxes, some subtraction produces a base value, the user may also enter the tolerance of the working value (base value), effectivlly giving rise to a small range to generate the permutations from.
What I would like to do then is find all permutations of the inventory (letters in the sheet) with their corresponding values right above them to the small range determined via the user (working value and tolerance). There are also quantity limits for each item. There four basic rules that need to be adhered to:
1) Permutation can't have both S and PP, so one or the other 2) Sum of H and HN must be greater or equal to sum of S and PP 3) H or HN or both must be present, so their sum cannot be zero 4) If HN is less than or equal to 2, and H is greater than 2, then NA and NG must equal 0.