I have got to this stage with a formula but cannot understand why it isn't working:
=IF(OR(A2=1,A2=2),(MIN(B2:B6)+50),(MAX(B2:B6)+50))
If A2 equals 1, I want it to add 50 on to the smallest (earliest) date in C2:C5.
BUT
If A2 equals 2, I want it to add 50 on to the largest (latest) date
The formula just adds 50 on the smallest (earliest) date, whether A2 equals 1 or 2.
What am I doing wrong?
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:
=CONCATENATE(WB_NEW!AH3," ",WB_NEW!AI3," ",WB_NEW!AJ3," ",WB_NEW!E3)
What I'm now trying to do is enhance this function to perform with 2 separate concatenation rules i.e. "if one of the cells specified above is blank, concatenate a different set of cells".
How do I create 3 color conditional formatting formulas for the following rules?:
if today is 15 days or greater before the day in the cell then green
if today is between 14 and 7 days before the day in the cell then yellow
if today is within 6 days to zero days before the day in the cell then results
anything is clear
x8:x21
5/10/2012
5/14/2012
5/21/2012
6/11/2012
6/12/2012
6/12/2012
6/12/2012
6/20/2012
6/22/2012
6/23/2012
7/4/2012
7/5/2012
7/8/2012
7/12/2012
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.
Hypothetically:
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)"
I have a cell in which I am entering either the letter "C" or "S".
I then want the following to happen......
If the cell contains the letter "S" do = IFERROR(VLOOKUP($B7,'Asset Growth Rate'!$A$2:$F$39,2,FALSE)*$C7,"-")
If the cell contains the letter "C" do = IFERROR(VLOOKUP($B28,'Asset Growth Rate'!$A$2:$F$39,3,FALSE)*($C28+$F28),"-")
The formulas themselves are already working in separate cells, I now just want to combine them depending on whether the cell contains S or C.....
I want to colour 10 cells (A1-J1) if I type in yes in cell K1. Using conditional formatting Im only able to colour 1 cell. Is this possible without using VBA?
View 5 Replies View RelatedIs 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.
A
B
C
D
E
1
1st
2nd
3rd
2
Jan
1
2
3
[Code] .......
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.
Attached is my spreadsheet.
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"
70>> "Good"
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?
View 2 Replies View Related******** ******************** ************************************************************************>Microsoft Excel - Book3___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD1ABC 2464104641046410 32081500002081520815 44062000040624062 51521259171521215212 Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
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.
View 7 Replies View RelatedI 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:
[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 really struggling with the following formula:
MATCH(MIN(ABS(Ann_TaylorRegularBust-B4)),ABS(Ann_TaylorRegularBust-B4),0)
I am trying to find the cell in the named row "Ann_TaylorRegularBust" that is closest to the input bust size in cell B4. This formula works when I use actual range instead of the named array.
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.
View 8 Replies View RelatedHow 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.
View 3 Replies View RelatedThis Subroutine:
[Code] .........
Seems to behave differently each time it is called from my main Loop. As an example the first loop finds:
TotalTubes = 6
TubeRows = 4
TopRowTubeCount = 2
Where 6/4 = 1.5 and is rounded up to 2
The controlling variable in my code for Length is TopRowTubeCount and it is a Public Variable Dimensioned as an Integer
However on the next loop it finds:
TotalTubes = 210
TubeRows = 4
TopRowTubeCount = 52
Where 210/4 = 52.5 and is rounded down to 52?
I even used the round function for the TopRowTubeCount calculation and the erratic behavior continues.
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.
View 4 Replies View RelatedI 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).
View 9 Replies View Relatedhow 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.
View 3 Replies View RelatedI have two conditional formatting rules that work independently, but I am looking to combine them into 1 rule, making (2) subject to (1) when (1) is true
(1) =$B3:$B6="y"
(2) =AND(COUNTIF(K$3:K$6,MIN(K$3:K$6))=1,K3=MIN(K$3:K$6))
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.
Like the example below
CUSTOMER idCUSTOMER NAME TYPEnew
737346 Nikos 81 737346
1154765 Kyriakos 81 1154765
1154765
1154780 Thanos 81 1154780
I have a file which I want to copy the lines.
I have made a test file for clarity. See annex.
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 want something strange but i guess you can do just about anything with excel these days if you ask around.
I want names for the sheets that are contained on a specific excel file that i have to be automatically generated accordingly to the following:
sheetname = runxyy_s=zz%
Where x is the lowercase letter of the word formed on the name of the first sheet of the workbook
and yy and zz are values stored on specific cells on the sheet,lets say yy is A1 and zz is H4.
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.