## New answers tagged worksheet-function

0

You can use an AVERAGE array formula, e.g.
=AVERAGE(IF((Race=1)+(Race=2),Pupil_Score))
confirmed with CTRL+SHIFT+ENTER
The "+" effectively gives you an "OR"
You can easily add extra conditions by adding IF functions, e.g. assuming "range1" needs to be "x"
=AVERAGE(IF((Race=1)+(Race=2),IF(range1="x",Pupil_Score)))

0

Without any sample data provided this is a little tough to envision but I suspect your formula could have a nested SUMIF that raised the total in the IF criteria's SUM. Something like,
=IF(SUM(J19:J29, SUMIF(L19:L29, "X", K19:K29))=Quote!J55,Quote!J55, "ERROR")

1

I don't have Excel to test these formulas right now, but I thought you could try one of the following:
=AVERAGEIFS(Pupil_Score,Race,">=2",Race,"<=3")
This works only if race numbers are adjacent.
The next one needs to be confirmed with ctrl+shift+enter instead of enter:
=AVERAGEIFS(Pupil_Score,Race,{2,3})
Not sure if it will work though. Give it ...

1

The AVERAGEIFS criteria come through as AND conditions. You require an OR condition since any one row could not be both 1 and 2.
In the above, the sum of the blue/greens is 23, their count is 5 making the average 4.6. The formula in E2 is either,
=SUMPRODUCT((C2:C8)*(B2:B8=F1:G1))/SUMPRODUCT(--(B2:B8=F1:G1))
... or with the colors hardcoded as,
...

0

Based on what you said in the comments you can do this one of two ways. You can either make a conditional statement based on their input or you can make the assumption that the users won't fill out both sections of data.
If you are using a conditional statement use this:
=IF(O9="",IF(S9="",HOURS(K9-G9),HOURS(O9-S9)),HOURS(O9-S9)
You are basically saying ...

0

To keep the random value formulas from updating, you need to overwrite them with their values. To do this, copy the range and Paste Special > Values back into the same range. This will remove the formulas and leave only the values.
If you want to disable manual editing (e.g., accidental deletion or keyboard input), you'll need to lock the range with the ...

2

The number of hours will be simply the next business day at 9:00 AM minus the starting time (formatted as [hh]:mm to allow the display of >24 hour periods). The trick will be getting the number representing the next business day at 9:00 AM correct.
The formula in B2 is,
=WORKDAY(A2,1*(HOUR(A2)>9))+TIME(9,0,0)
This makes the formula in C2,
...

0

Here is my take on your sample data and requirements. Note that I have expanded your sample data to demonstrate how multiple scenarios are dealt with.
The standard formulas in D2:G2 are,
D2 is =IF(COUNTIF(A:A,"Widget")>COUNTIF(D$1:D1,"Widget"),"Widget","")
`
E2 is ...

1

Updated formula. Finds the cell with the first type and second type and looks for the word widget between it. If it exists return the value beside type if not return blank.
=IFERROR(IF(MATCH("widget",INDIRECT("A" & SMALL(IF(A:A="type",ROW(A:A),1000000),E1) & ":A" &
...

1

If your formula returns "1" or "0" then the quotes make those text values and SUM will ignore text.
Remove the quotes and use just 1 and 0 and the sum function will sum accordingly...
.....alternatively, as counting 1s is the same as summing them you could use COUNTIF to count "1"s without changing your original IF formula, e.g.
=COUNTIF(J2:J2013,"1")

0

You can map OneDrive as a drive letter if you want.
This is not a direct answer to your question, but it may help you get to where you need. The only caveat is that the drive letter would need to be the same (or adjusted) if you use the spreadsheet in a different environment.

0

You can do this with an IF AND statement like:
=IF(AND(A1="Widget",A2="**Type**"),B2,"")
That basically tells Excel IF the text "Widget" is in a cell above a cell with the text "**Type**", enter the data from the cell in column B next to "**Type**", otherwise, leave it blank.
You end up with this:
If you want the data (072 in this example) to show up ...

1

From your description you're either paying the full rent (1-6 months), half the rent (7-9 months) or nothing at all (10+ months). You might also want to input future values but have the formula reflect that you are not responsible for the stipend until they actually move in.
The formula I've used in C2 to calculate the prorated stipend (according ...

0

Here’s a more general solution. I’ll assume that you have five “value” columns (B through F)
and you want the result in Column G. Type this formula into cell G2:
=OFFSET($B$1, 0, MAX(COLUMN(B2:F2)*NOT(ISBLANK(B2:F2)))-2)
and type Ctrl+Shift+Enter. That makes it an “array formula”;
it will show up in the formula bar with braces ({…}) around it.
Now ...

1

I would put the date of origin of the rent in a column (A), and then the full rent in a column (B). Then I would put Current Rent in column C, and I would fill the cells in column C with the following formula:
=IF(TODAY() > A1+182.5, B1/2, B1)
I use 182.5 for half a year. That way, you get the following:
A B C
11/19/2013 600 ...

1

If it is just three value columns, a simple way to do it would be IF tests. Say the table you show starts in A1 (A1="ID" heading). The formula for the first output value would be:
=IF(ISBLANK(D2),IF(ISBLANK(C2),$B$1,$C$1),$D$1)
Copy that down the output column. (This assumes every row in the data table is represented by a row in the output table ...

2

Enter =sqrt((b2^2)+(c2^2)) in D2,
then move back the selection (cursor) to D2.
Note that there is a tiny black square at the lower right of the selection frame.
Grab the tiny square with the mouse pointer, then pull it down onto all lines where you wish to have the formula.
... done.
Note: Works exactly the same in Excel.
Additional info:
To lock a ...

0

To recap and correct,
B6 = number of questions answered correctly
C6 = total questions asked
So the portion of questions answered correctly is B6/C6.
For B6 = 15 and C6 = 50, this will evaluate to 0.3.
You can display this as 30% by formatting the cell as Percentage,
or you can simply multiply by 100.
By the way,
... so B6/C6 is equivalent to ...

2

Select the table goto insert>pivot table.
In the pivot table select Schoolname as the row label and QTY as the value (defaults to sum).

0

Try using FLOOR() and CEILING.
Let's say your first age to check is in cell A1 and you want to group all ages into bands of 10. FLOOR() rounds down to the nearest number you specify, while CEILING() rounds up.
Then simply put an IF() on the front to deal with your upper limit.
=IF(A1>=50,"50+",FLOOR(A1,10)&"-"&CEILING(A1,10)-1)

1

Set up an area somewhere in your workbook that looks something like this:
X Y
1 20 20-29
2 30 30-39
3 40 40-49
4 50 50+
Then, if your age values are in column A starting in row 2, enter
=VLOOKUP(A2, X$1:Y$4, 2)
into cell B2 (or wherever you want the group ID displayed) and drag it down.
The VLOOKUP ...

0

You could do this with a VLOOKUP command or a MATCH INDEX one like below:
Notice how I handle the error of not finding anything too.
Simply drag this formula down to fill out everything.

0

Just to summarize what was in the comments section.
Kyle has two cells:
C6 = Total questions
B6 = Questions asked
Here is what he wants to find:
Percentage questions not asked = (C6-B6)/B6
Percentage questions asked = 1-((C6-B6)/B6)
Using the "1 -" will inverse the original result.
This may seem basic to some but hopefully it helps others.

1

First save your file than enter this formula anywhere
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

0

Merged cells are just formatting. The issue is correctly pointing to the cell that has the value. One approach would be a helper column where Excel can find what it needs (either the actual value or a pointer to it). This is a lot more practical if every row is associated with either an actual value or a "merged value". If some rows can have no value, it ...

0

Here is another way (using VBA) to know if a cell is part of a merged region.
I sometimes use this function (and some others derived form this one) :
Public Function IsMerged(AnyRange As Range) As Boolean
Dim MergedRegion As Range
IsMerged = AnyRange.MergeCells
If IsMerged Then Set MergedRegion = AnyRange.MergeArea Else Set MergedRegion = ...

0

For a worksheet formula:
=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))+TIME(MID(A1,12,2),MID(A1,15,2),RIGHT(A1,2))
and then format it as you like.
If you want to use a VBA macro, the following assumes
Your data starts in A1 and is in column A
The results will go in Column B
-- but you can easily change the code to overwrite, once you see it is working ok
...

0

While there is an NEI (Not Enough Information) flavor to your question, if the value column P is usually a number or date and you want the value from column L is column P contains #N/A or N/A or #NA or even an actual Excel #N/A error code then perhaps something like this would do.
=IFERROR(--P1, L1)
Please remember that we are not mind readers and our ...

0

In U1 enter:
=IF(ISNA(P1),L1,P1)
and copy down.
NOTE:
the obvious =IF(P1="#N/A",L1,P1) will not work!

0

In MS Excel cell contains formulae as well. The formulae entered in cells are used for calculations. For specifying that a cell contains a formula always start the formula with an =sign.
A function is a piece of code designed to calculate specific values and are used inside formulas.

2

I will usually do this in either of the following ways:
I will get the skinny plus drag the formula down then drag the cell
with the formula to where I want it. Then I will drag the formula up
in the original column which will copy back the formula. (see image below)
Or
I will put an apostrophe in before the equal ("=") sign which will
turn the ...

1

It sounds like you may have recently used the "Text to Columns" function and changed the delimiter to commas or spaces or something.
When you do that, Excel continues looking for the new delimiter in whatever you paste in. To revert to the old behavior, just go back into "Text to Columns" and change the delimiter back to Tabs.

0

Select the cells and run:
Sub DateFixer()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
For Each r In Selection
v = r.Text
r.Clear
r.NumberFormat = "dd/mm/yyyy hh:mm:ss"
r.Value = DateSerial(Mid(v, 7, 4), Mid(v, 4, 2), ...

0

This worked fine for me
Sub dothis()
Dim row As Integer
row = 0
Dim maxRows As Integer
maxRows = 3
For row = 1 To maxRows
Range("A" & row).Value = Replace(Range("A" & row).Value, ".", "/")
Next row
End Sub
Simply, on a UK PC any way, it didn't like 25.08.2011 because of the period (.). So, I convert the dot to a forwardslash.
After this, ...

0

Say we are using Sheet1 and Sheet2. In Sheet2 cell A1 enter:
=IF(Sheet1!A1="","",Sheet1!A1)
Then copy A1 to B1
Then copy both A1 and B1 downwards.

0

There might be a fancier way to do this but here is one solution. The example in the picture is more generalized than the description so I'll base the solution on that. You can add or remove columns from the formula as needed. This would go in E2:
=IF(OR(A2>"c",B2>"c",C2>"c",D2>"c"),"fail "&IF(A2>"c",A$1&" ...

1

=INDEX($1:$1,MATCH("!",$2:$2,0))
Match finds a value within a range of cells, in this case you're looking for the exclamation point in row 2.
Index returns a specific position within a range. The range is row 1, and the returned position is provided by the Match function.

0

I know the OP specifically stated that the list came from a range of cells, but others might stumble upon this while looking for a specific range of values.
You can also look up on specific values, rather than a range. This will give you the number where this matches (in this case, the second spot, so 2). It will return #N/A if there is no match.
...

0

Perhaps you can adapt this solution to meet your actual set-up.
Assuming that the data you wish to average is in Sheet1 A2:J10, with the headers for that data in A1:J1, and that you enter the e.g. three headers of the columns for which you wish to perform the average in Sheet2 A1:C1, this array formula** will give you the desired average:
...

0

Paste the following code into the worksheet with the data and update "Sheet1" to the sheet name with your auto-filter.
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet1").AutoFilter.ApplyFilter 'update "Sheet1" to the name of your sheet with auto-filter
End Sub

0

You could use this:
=IF(D19=E19,"Values are accurate",(D19-E19)/MAX(D19:E19))
This scales your differences between -100% and +100%.
So, if you have 3 system stock and 1 physical, the value it will be +67% off (instead of +200% as by your previous formula). If you can live with that, this solution should work.

0

Something like -
=IF(D19=E19,"accurate",IF(E19=0,magic formula,(D19-E19/E19))
But the magic formula will only work if you know, mathematically, that your scale is absolute.
For instance a change from 1 to 2 is 100% and -1 to -2 is -100%. But, from 0 to 1 is only a change of 10% if the scale is 0-100. If the scale is 0-3, it's 33%, etc
One magic formula ...

-1

Two possible solutions that I can see
A)how about you wrap a IF statement around your existing IF, to cover for conditions like E19=0 or E19!="" (i'm not sure if != is the right excel syntax for not equal to) E19!="" means it's equal to something. You have to decide what value you want when E19 is 0, then have it covered by your conditional..
OR
...

0

NOW() returns the current date and time as a floating point number. The integer portion is the number of days that has passed after a certain date and the decimal portion is the time that has passed since midnight.
I suspect that when the SUMIFS() function returns the incorrect value is that it need updating since Excel did not recognize when the value of ...

0

I applaud your effort to generate two sequences from one formula,
but I submit that this is hard to maintain.
I recommend that you establish the two sequences in two separate columns (“helper” columns),
and then fuse them in column A. For example,
A1 — =IF(C1="TOTAL", X1, Y1), and drag down
X1 — =IF(C1="TOTAL", 1, 0)
X2 — =IF(C2="TOTAL", ...

0

If I read your question correctly, this is an accounting application where you are relying on the word "RECEIVED" to treat the amount as a credit. If that is correct, you would need to add an expression to Raystafarian's solution:
=SUMIF(B5:B10,"<>"&"RECEIVED",A5:A10)-SUMIF(B5:B10,"="&"RECEIVED",A5:A10)
Note that this relies on a ...

0

You could, for instance, put this in cell A11 for sum of range A5:A10 where B5:B10 is not "received".
=SUMIF(B5:B10,"<>"&"received",A5:A10)

0

I have come across this issue. It happens when you have a lot of cells to be selected, along with this it depends on your hardware because Excel 2013 consumes a lot of RAM.
To solve your issue.
Close other unnecessary apps this would free some RAM memory
After you do the CRTL+SHIFT+END, Please wait for sometime until the
excel finishes selecting the ...

0

I would use the Fuzzy Lookup Add-In for this. It will read in two tables and return the best match (or matches), along with confidence and similarity scores.
http://www.microsoft.com/en-us/download/details.aspx?id=15011
I cant follow your second paragraph above, but I think for your scenario I would run a first pass using the first pair of columns. Then I ...

0

With any luck you will have Excel-2007 or higher and can use this array formula.
=SUMPRODUCT(IFERROR((Sheet3!$E$5:$E$900<>"")*(Sheet3!$M$5:$M$900<>"")/COUNTIFS(Sheet3!$E$5:$E$900,Sheet3!$E$5:$E$900&"",Sheet3!$M$5:$M$900,"<>"),0))
An array formula require Ctrl+Shift+Enter to finalize rather than simply Enter.

Top 50 recent answers are included