I have a range that contains a mixture of hard coded data and data pulled from a database sheet. I want to average only the hard coded data not the ones in the cells containing formula. I need this to be dynamic instead of just selecting the cells as formula might be added later on.
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?
if-and-or statement if possible. The formula would go in cell A10.
If D5="x" and D4=7, answer would be "H8", if D4 is blank, answer would be "x". If D5=2 or 3, and D4=7, answer would be "H8", if D4 is blank, answer would be "v8". If D5=1 or blank and D4=7 or blank, answer would be " ".
I am a sales rep and am designing a spread sheet to help me track my commissions, on occasion I need to split the commission with another rep I have the spreadsheet set up as follows:
J1 Dollar amount - the total amount of the sale. K1 fee% - my fee is negotiable so this number will vary by client this field is formatted as a precentage. L1 total commission - takes J1*K1 to give me a $ amount M1 split y/n - here is my question, i would like a formula that will divide L1 in half if I type a Y or Yes in M1 I'm sure this can be done but I dont know how, also would the formula go in L1 or M1 if I want the results to be displayed in L1?
I'm entering a simple IF statement in excel that asks if one cell is bigger then another. However one of the cells I'm referencing is also a formula, so excel is not reading the value in that cell. How can I get around this problem? Is there anything that will enable me to compare just the value of that cell? I have attatched the file and the cell in question is F21.
How to increment a formula with an If statement that transforms 2 values in time format and then as long as these two values keep beeing added in the cells the formula keeps active in the column.
I tried to apply the code for all the but this makes my file huge like 20MB!
You can check this on the file, go the "records" sheet, add the time in the "shift start" and "shift end" fields, then the difference value goes to "hours" in the sheet.
"your formula is missing --) or (. Check the formula and then add the parenthesis in the appropriate place. The following is one long string. I broke it up to make it easier to read.
Is there a way to make the last two parts ("height" and "width") of the OFFSET statement formula static (meaning they alway refer to the same cell)? I am inputting a different number in the "colum" section of the formula and every time I do...it changes which cells the "height" and "width" refer to.
I am creating an electronic timesheet. I have created drop downs for times of the day, which then automatically calculates the number of hours worked based on the times selected. The trouble I am having is, I also need to incorporate "Vacation", "Sick Day", etc into the drop down box where the time of day is located.
The sheet works great until someone selects a non-time related answer, such as "Vacation". When this option is selected, the cell that is supposed to contain the number of hours worked says #VALUE! when I, in fact, need it to list a value of 0 for the number of hours worked. Any idea on how to combine my formula =(D6-D5)*24+(D8-D7)*24+(D10-D9)*24 with an IF statement that will automatically assign a value of 0 if a text answer is selected from the drop down?
I am trying to use an If statement and wanting to evaluate another formula.
The formula is as follows: =IF(B6>=7,Table!M16,Table!M47) where B6 =REPLACE(D24,1,3,"") which returns an integer. In this case it is 6.
Unfortunately it doesn't process the formula value correctly in the If statement unless the reference cell is hard coded with the integer. Is there a way to force the formula to evaluate the value of the cell formaula first before doing the If comparison?
Round to Nearest Half Based On Defined Range of Decimal thread.
So here is what happens in the first half of the equation....I round numbers to the nearest half based on a specific range of decimals in the original number. For example if the number in A1 is:
28.0 to 28.399 then rounds DOWN to 28 28.4 to 28.799 then rounds to 28.5 28.8 to 28.99 then rounds UP to 29
For this I am using shg's equation:
[Code] ........
This is all I needed for most of my numbers.....but one part goes one step further.....
NOW THE SECOND HALF......Once the first half of the equation rounds the number above, I need the second half of the equation to then do the following. If the resulting number from the first part of equation above is:
1 through 3 = its own size (1 is 1, 1.5 is 1.5, 2 is 2, 2.5 is 2.5, 3 is 3) 3.5 to 7 = 3 7.5 and above = 4 Resulting number from last part of the formula multiply by 2
So this will end up being only 1, 1.5, 2, 2.5, 3, or 4 multipled by 2.
I am pretty sure this second part would be an IF statement (or maybe there is a more efficient way), but I am still learning the ins and outs of that....but I don't know how to use the first formula and add the needed second part into all one formula.
In the attached spreadsheet, I have data pertaining to the amount of money raised for a bunch private equity funds, and the dates when this money was raised.
For a little background, private equity funds raise money in various batches or "closes", so a fund may initially raise $5M in its "first close" and then in its "second close" it may raise another $5M or so. And then a "final close" would be the last time that fund would raise money.
I'm trying to figure out a way to move data in my "Fund Size" and "Fund Size Date" columns to the corresponding "First/Second/Final" close amount/date columns, according to the "Status" of the fund in my Column A.
Essentially, if a fund is either "Fundraising" or "Fundraising and Investing" status, then I would need the fund sizes and fund size dates to populate in the corresponding columns left to right.
And then if a fund is in "Investing" status, the most recent values (or those furthest right) would need to appear in the "Final Close" columns.
I have a formula which is currently too long and which I'm sure could be done more efficiently. That plus Excel is saying the formula is too long. I have pasted the formula below and broken it up for clarity:
How the Lookup will break down within an IF statement?
Original
HTML Code: 'ActiveCell.FormulaR1C1 = _ "=IF(R[-1]C[-2]"""",LOOKUP(1000,SEARCH({""Principal"",""Foreclosure"",""Interest"",""Premium"",""Discount""},RC[-1]),{1,1,2,2,2}),"""")"
Non-Working If statement. I realize that the Lookup isn't currently here. Including it kind of defeats the purpost of breaking this out into IF's, doesn't it?
HTML Code: If MyFirstCell.Offset(0, -2) "" Then MyFirstCell.Offset(0, -1) = Search("Principal") MyFirstCell = 1 ElseIf MyFirstCell.Offset(0, -1) = Search("Foreclosure") Then MyFirstCell = 1
I have lots of data in the form of names, and i want to extract all rows of data for that name. So if name "john smith" appears in column A 7 times, i retreice 7 rows of data.
This works perfectly and it gives me my 7 rows of data if i type in "john Smith" into A10, however, i want one more condition to be met in column C, whereby Column C is made up of either 1, or 0. If its a 0 i am not interested, if it is a 1, i am.
How I might be able to create an IF statement that would look at various cells and if empty, would go to the next indicated cell, ect, until it found data and then conduct the sum analysis indicated in my formula below.
The cells identified in my formula, which look for "", I want to skip to the next as stated above if empty.
I use the If - Then statement in VBA to determine a condition which works perfectly fine for the particular cell I reference to, but i need it to apply to all the cells i.e. cells A1:A10?
Sub MACRO1()
If Range("A1") = "PAYE" Then Range("B1").Formula = "=C2 * .128" End If
If Range("A1") = "LTD" Then Range("B1").Formula = "=(C2 - 100)* .128" End If
I have a statement that runs to check for empty cells but i want it to only run when cells in column A contain data and this can change from time to time. Eg sometimes column A will contain 10 items, sometimes more sometimes less, sometimes none.
Need a statement that will only run my statement when these cells contain data.
HI can't seem to get this last scenario laid into this if statement. I am having trouble with the second statement. I need the three cells to equal 100, but only when all three are filled. If only two as in this scenario I would like % Error in cell G2.
Sheet3 *DEFG250%50%*O.K.Spreadsheet FormulasCellFormulaG2=IF(SUM(D2:F2)=0,"",IF(AND(OR(ISBLANK(D2),ISBLANK(E2),ISBLANK(F2)),SUM(D2:F2))=1,"% Error",IF(OR(D2>E2,F2>E2,SUM(D2:F2)1),"% Error",IF(SUM(D2:F2)=1,"O.K.",IF(AND(E2>D2,E2> Excel Jeanie HTML 4
I have a worksheet that has 5 rows and 5 columns. The five rows are Highschool subjects. Math, English, French, Phys. Ed, History. The Five columns are Monday, Tuesday, Wednesday, Thursday, Friday. I want to device a worksheet that say for Monday it will have YES for math, no for english, yes for French, yes for phys. ed, no for history. Tuesday would be say no for math, yes for english, no for french, etc, etc. I then want to use an if statement to convert the yes & no entries to a numeric value. I.E I have this right now. =If(B6="YES",1,0)
This simply puts a 1 for a yes and 0 for a no into cell M6. What I want tho is each subject to add the amount of yes's for the week and put it into cell M6. So if math is taken 3 times a week it would put a 3 into cell m6. if History is taken 4 times a week it would put 4 into cell m7, etc, etc.
cell AT7 is a formula which brings back a numerical value i.e. 15.
in cell AS7 i need a formula that displays the value of cell AT7, as long as that value is less than 15, if the value is 15 or higher then 0 should be displayed.
I dont know why i cant get this, seems like a simple IF statement to me, but who knows.
Once this formula is done i need to drag it down about 2500 cells and autosum to get a column total...
