# Posts by AAE

• ## AverageIf #DIV/0! error

Re: AverageIf #DIV/0! error

Just change the last argument to return a blank.

=IFERROR(AVERAGE('Week 1'!G4,'Week 2'!G4,'Week 3'!G4,'Week 4'!G4,'Week 5'!G4),"")

• ## Sparklines when condition met

Re: Sparklines when condition met

Change the sheet name as needed. Briefly tested, no issues, modify to suit your needs.

• ## Formula to return which quartile a given number falls in

Re: Formula to return which quartile a given number falls in

With Excel there is generally more than one way . . .

Another option:

=LOOKUP(A1,{0,0.26,0.51,0.76,1.001},{1,2,3,4,"N/A"})

• ## Add a trend arrow to dashboard

Re: Add a trend arrow to dashboard

Probably best to upload a sample workbook so we have sufficient details of your needs if answer is not what you need/want.
I will assume you want to track the trend for the current month compared to previous month.

We'll use Conditional Formatting using an icon set.

Q7 formula: =--(INDEX(E7:P7,COUNT(E7:P7))>INDEX(E7:P7,COUNT(E7:P7)-1))

(Note: you can format the cell font to be same color as cell to hide the formula result so that only the arrow is visible in the cell)

Select Q7 and click on Conditional Formatting (Home tab in ribbon).
Choose the Arrow icon set, then edit the rule.

Icon1: green up arrow
Value1: Select >, then enter 0 (zero) in the box
Type1: set this to use Number type

Icon2: change yellow arrow to red down arrow
Value2: Select >=, then enter 0 (zero) in the box
Type2: set to use Number

Icon3: red down arrow (default)

Alternately, you could use a Sparkline in Q7.
Select Q7 then select Insert on Ribbon Menu, locate and click on "Line" in the Sparklines section.
Select E7:P7 as the data range, click OK - cell will show a trend line for the data.

• ## More than one conditional formula

Re: More than one conditional formula

Quote

In Cells:
B16=1
B17=2
=IF((B16<>B17 AND B16<B17),"TRUE","FALSE")
#NAME?

All you need is: IF(B16<B17,TRUE,FALSE)

If B16 is less than B17 then it is always going to be "not equal to", thus you only need to test for the one condition.

• ## Help with Reset Counter when condition Met

Re: Help with Reset Counter when condition Met

Another variation, slightly different - works the same as that given by richadj4

=COUNTIF(OFFSET(\$B\$1,MAX(--(\$B\$2:\$B\$1000="E")*ROW(\$B\$2:\$B\$1000)),0,10,1),"x")

For formula simplicity and to slightly reduce array calculations the above formula uses a fixed value ( 10 ) for the row size argument - you can change this to a higher value to pad in extra rows. This argument in the OFFSET function defines how many rows to include in the range

Or to auto-calculate how many rows are below the last occurrence of "E", use this.

=COUNTIF(OFFSET(\$B\$1,MAX(--(B2:B1000="E")*ROW(B2:B1000)),0,COUNTA(B2:B1000)+2-MAX(--(B2:B1000="E")*ROW(B2:B1000)),1),"x")

It, too, is an array formula and must be entered using the Ctrl+Shift+Enter keys in combination

• ## decimal value derived through formula acting as text, how to make it as value

Re: decimal value derived through formula acting as text, how to make it as value

Quote

when i am testing it IN Cell B10, with IF formula, like =IF(B9=0.7,7,0), it shows false results, and returns 0 (return value on non fulfilling conditions)

I tried using VALUE function also which works with INT / TRUNC function (but not with TRUNC or MOD), but no desired result.

With the INT function you referenced:
=IF(ROUND(A9-INT(A9),1)=0.7,7,0)

• ## Require Cells to have scroll down category based on other filled cells

Re: Require Cells to have scroll down category based on other filled cells

Quote

I am needing to REQUIRE the drop down list option IF they have filled out the time.

The method I provided will only show the drop down list if both time entries are made. To be clear: no time entries, no drop down list.
How does this approach not fit your described need?

I did forget to mention that you must use Data Validation > Custom option for this method to work.
Perhaps you did not implement it correctly due to that oversight.

• ## Pivot Table with Multiple Results

Re: Pivot Table with Multiple Results

Select the data range needed for the Pivot Table (Columns L:P per your example).

Ribbon > Insert > Pivot Table > New Sheet

Drag "Recruiter" down to the Row Labels section of the Pivot Table Field List
Drag "Recruiter" (again) down to the Values section - it should default to "count", but if not, click on "Count of Recruiter" and select Value Field Setting and choose the count option.

Drag "Source" to the Report Filter section.

You will see the Pivot Table update itself to the layout as you drag the fields.

The Report Filter will automatically default to "ALL". From this point just click the drop down and choose "Manager Referral" to see totals for this selection only.

Keep in mind that Pivot Tables must be refreshed as the data are changed. Just right click the PT and choose refresh from the context menu.

• ## Require Cells to have scroll down category based on other filled cells

Re: Require Cells to have scroll down category based on other filled cells

OK - reread your post, understand now what you need, You want to lock out the drop down selection until after time entries are made.

Let's say the time entries are made cells B2 (in) and C2 (out).

If cell A2 contains the data validation drop down list, we could use this formula to lock out the list if time entries are not made
Here is the formula:

=IF(AND(ISNUMBER(B2),ISNUMBER(C2))=TRUE,INDIRECT("List"),"")

In the formula "List" is the named range containing the list of selections for the category drop down.

Obviously, you must adjust the cell references and change "List" to the actual named range.

• ## Require Cells to have scroll down category based on other filled cells

Re: Require Cells to have scroll down category based on other filled cells

Take a look at this link at Contextures

Create Dependent Drop Down List

This is a commonly asked question and there are plenty of posts on the forum.
Do a search using Dependent Data Validation or Dependent Lists as the search terms.

• ## Sum two columns with changing ranges

Re: Sum two columns with changing ranges

See attached - seems to do what you ask, but not extensively test (your job).
Study the formulas and adjust range and cell references accordingly.

The following named ranges are used:
CountRw =COUNT(CurrentYr)
CurrYrCol =MATCH(YEAR(TODAY()),Sheet1!\$4:\$4,1)

A Pivot Table would be useful here.

forum.ozgrid.com/index.php?attachment/70851/

• ## Excel says: You've entered too many arguments for this function:

Re: Excel says: You've entered too many arguments for this function:

Try this:

=IF(F20="","",IF(and(F15=\$D\$15,F16=\$D\$16,F17=\$D\$17,F18=\$D\$18,F19=\$D\$19),RANK(F20,\$E\$20:\$AE\$20),"FAIL"))

Nesting formulas can get messy if not careful about the parentheses.

I generally set them up as blank arguments then fill in

=IF(,,IF(AND(,,,,,),RANK(,),"FAIL"))

Obviously, each comma separates the arguments. If you set up the nesting structure first before filling in arguments it is generally easier, IMO, to prevent mistakes with the parentheses.

TIP: learn to make use of the Evaluate Formula Tool to step through formulas - you will learn how Excel evaluates the arguments and see where mistakes occur.
The tool is located on the Ribbon - Formulas > Evaluate Formula. Just select any formula cell before invoking the tool.

• ## Sum non-consecutive/contiguous cells by Month and Year

Re: Sum non-consecutive/contiguous cells by Month and Year

Here is a tip - one of the best features, IMHO, in Excel is the Evaluate Formula Tool found on the Formulas tab in the ribbon.
This tool lets you step through a formula and see how each argument is evaluated - excellent for learning. Of course, you do need to know something about the function syntax and what is required in the argument but with all the built in help in Excel this, too, is easy to learn.

To use the tool simply select any cell with a formula then click Evaluate Formula

For Pivot Tables I highly recommend a visit to YouTube and just search on them. Lots of good help there for nearly anything you want to do in Excel.

• ## Divide a number by a different percentage depending on value

Re: diivde a number by a different percentage depending on value

=i2/lookup(j2,{0,2300,2800,3200,3400},{0.4,0.5,0.65,0.85,0.01})

• ## Display selection based on a value in another cell

Re: Display selection based on a value in another cell

A more flexible approach using Excel Tables - see sheet2.

Excel Table created to maintain list of countries and cities.
Named Ranges are based on the Table

Country: =INDEX(Table1[COUNTRY],1):INDEX(Table1[COUNTRY],COUNTA(Table1[COUNTRY]))

forum.ozgrid.com/index.php?attachment/70843/

• ## YTD Figures

Re: YTD Figures

Good job on finding a solution on your own - always feels great to figure things out. Thanks, too, for posting your solution for the benefit of others on the forum.

It seems you could use a Pivot Table to get the results you are looking for - have you tried using a PT?
You could convert the data range into an Excel Table, which Excel will treat as a dynamic named range. Use the new Table as the data source for the Pivot Table then just refresh the PT whenever data are changed.

• ## Listing Unique Names

Re: Listing Unique Names

Quote

Is there a formula, or method, whereby I can create a separate worksheet that lists out each unique customer and their total purchases?

Have you considered using a Pivot Table? It may be placed on a different worksheet and should allow you to easily configure a report that displays totals per customer.
You can also filter the PT to show only the top-X values (i.e. 5, 10, etc), plus you may sort the table.

While Advanced Filter will certainly provide you a list of unique values you will have additional work to perform to calculate the totals - possibly using a SUMIF formula.
My recommendation is to use the PT.

• ## Sum non-consecutive/contiguous cells by Month and Year

Re: Sum non-consecutive/contiguous cells by Month and Year

Pivot Table sounds like a good choice for this.

=SUMIFS(\$C\$10:\$C\$15,\$A\$10:\$A\$16,">=11/1/2016",\$A\$10:\$A\$15,"<=11/30/2016")

You could use cell references for the dates rather than hard coding them into the formula
=SUMIFS(\$C\$11:\$C\$16,\$A\$11:\$A\$16,">="&E9,\$A\$11:\$A\$16,"<="&\$F\$9)

• ## Count - Attendance Formula

Re: Count - Attendance Formula

Another option, albeit not a formula:

Add a helper column adjacent to the data table and use it to extract the date using the formula =INT(B2), copied down the column.
Use Advanced Filter to copy unique values to another range.

In your example worksheet, assuming helper column is D (with a header of Date2), initiated Advanced Filter, select the range of cells (C1:D17), tick the option to copy to another location and tick the box "Unique records only").

You will end up with a list of unique entries and can then use a Pivot Table to get the counts.