## New answers tagged worksheet-function

0

If just changing the quotes from "" to """" doesn't fix it then maybe you should try setting the value property of the range object, instead of the range object itself.
Sheets("Invoice").Range("G11") = "=IF(ISNA(VLOOKUP(B11,INVENTORY!A$1:D$2501,INVENTORY!C$1,FALSE)),"",(VLOOKUP(B11,INVENTORY!A$1:D$2501,INVENTORY!C$1,FALSE)))"
Your are trying to set the ...

0

In VBA a " is used to enclose a string. To use a " inside a string, use "". What goes wrong in your formula is that VBA converts the middle two quotes to one quote. If you replace the "" in the middle with """", you should be good to go.
See this Stack Overflow link for reference.

0

You can use INDEX and MATCH with the rows offset by 2 or whatever number you need, e.g. for your example
=INDEX(A3:A6,MATCH("Bananas",A1:A4,0))
With a named search range you can do the same with OFFSET, i.e. with Fruit as the named range
=OFFSET(Fruit,MATCH("Bananas",Fruit,0)+2-1,0,1)
Where +2 defines the "offset"

0

SUMIF function does a conditional sum based on values in another range, e.g. for your example you can use this formula in E4
=SUMIF(A1:A3,"X",E1:E3)

0

Make a column F and in every cell:
=IF(A1="X";E1;0)
=IF(A2="X";E2;0)
=...
then form the sum over the contents in column F.

0

You can do this very simply by "offsetting" the date by the requisite number of days, e.g. 20th June until 1st January is 195 days therefore you can use this formula
=YEAR(P2+195)
format result cell as number with zero decimal places

0

You might want to shorten the formula somewhat with EDATE and EOMONTH.
The formula in B2 is,
=YEAR(EOMONTH(EDATE(A2,6),0)+(DAY(A2)>19))
Fill down as necessary. I've applied a custom number format using F\Y 0000. This leaves the year as a true number that can be used in future calculation while visibly displaying the fact that it is an ...

0

Awww, figured it out. I had to put in a nested if to check if the month = 6 and date is >= 20.
=IF(AND(MONTH(P2)=6,DAY(P2) >= 20),YEAR(P2)+1,IF(MONTH(P2)>=7,YEAR(P2)+1,YEAR(P2)))
I don't like to use nested IFs, but it seems like a necessary evil this time.

1

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)))
See example here

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

The only way you can keep the formula simple is if you can come up with criteria that are not mutually exclusive. For example,
=AVERAGEIFS(Pupil_Score,Race,">=2",Race,"<=3")
This works only if race numbers are adjacent.
Others have suggested array (CSE) formulas so I won't repeat that. Those formulas tend to be rather complex. If you want to keep ...

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 ...

Top 50 recent answers are included