Posts by AAE

AAE · Feb 17th 2017

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),"")

AAE · Feb 11th 2017

Re: Sparklines when condition met

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

Code

Option Explicit
Sub Addsparklines()


    Dim LastRow As Long, c As Range, sparkdata As Range, sparkrng As Range
    


    LastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    
    Set sparkrng = Range("O3", Cells(LastRow, 15)) ' range in which to place the sparklines
    Set sparkdata = Range("B3", Cells(LastRow, 13))


    With sparkrng
        .SparklineGroups.Add Type:=xlSparkLine, SourceData:=sparkdata.Address
        .SparklineGroups.Item(1).SeriesColor.ThemeColor = 5
    End With
    
    Application.ScreenUpdating = False
    
    For Each c In Range("O3:O" & LastRow)
        If Range("A" & c.Row).Value = "Name" Then
            c.SparklineGroups.Clear
        End If
    Next c
    
    Application.ScreenUpdating = True


End Sub

Display More

AAE · Feb 6th 2017

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"})

AAE · Feb 6th 2017

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.

AAE · Feb 6th 2017

Re: More than one conditional formula

Quote

In Cells:
B16=1
B17=2
=IF((B16<>B17 AND B16<B17),"TRUE","FALSE")
Answer:
#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.

AAE · Feb 4th 2017

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

AAE · Dec 24th 2016

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)

AAE · Dec 21st 2016

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.

AAE · Dec 17th 2016

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.

AAE · Dec 16th 2016

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.

AAE · Dec 16th 2016

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.

AAE · Dec 12th 2016

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)
CurrentYr =OFFSET(INDIRECT("Sheet1!"&ADDRESS(5,CurrYrCol)),0,0,12,1)
CurrYrCol =MATCH(YEAR(TODAY()),Sheet1!$4:$4,1)
PriorYr =OFFSET(INDIRECT("Sheet1!"&ADDRESS(5,CurrYrCol-1)),IF(CountRw<12,CountRw,0),0,12-CountRw,1)
Year_Header =OFFSET(Sheet1!$A$4,0,1,1,COUNT(Sheet1!$4:$4))

A Pivot Table would be useful here.

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

AAE · Dec 12th 2016

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.

AAE · Dec 11th 2016

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

Glad to be of help.
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.

AAE · Dec 11th 2016

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})

AAE · Dec 11th 2016

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]))
City_List: =INDEX(Table1,1,MATCH(Sheet2!G11,Table1[#Headers],0)):INDEX(Table1,COUNTA(INDEX(Table1,,MATCH(Sheet2!G11,Table1[#Headers],0))),MATCH(Sheet2!G11,Table1[#Headers],0))

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

AAE · Dec 11th 2016

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.

AAE · Dec 11th 2016

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.

AAE · Dec 11th 2016

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)

AAE · Dec 11th 2016

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.