Solved

Need help combining 4 Excel formulas please

Posted on 2013-06-05
5
224 Views
Last Modified: 2013-06-19
Note: this is a follow-up to this thread:
    Use a function as the criteria in the Excel =COUNTIF() function

Hello,

The screenshot (with details) shown below is from an Excel (2010) spreadsheet which is attached. The purpose of the spreadsheet is to:

    Display the Running Total of Col A dates which are in 2013 but are not Sundays.

The objective has already been achieved (I believe) in the attachment with the results shown here in Col G:

Fig.The data range includes Rows 2:100 and has the following columns:

    Col A_a random date between 2011-Jan-01 & 2013-Jun-05
    Col B_the day of the week
    Col C_spacer
    Col D_shows dates in 2013 ("`" if not)
    Col E_shows non-Sundays ("`" if not)
    Col F_shows when both Cols D & E are True ("`" if not)
    Col G_shows running total of "Boths" in Col F

    A2    =RANDBETWEEN(40544,41430)
    B2    =TEXT(A2,"ddd")
    C2       n/a
    D2    =IF(YEAR(A2)=2013,2013,"`")
    E2    =IF(B2<>"Sun","NotSun","`")
    F2    =IF(AND(D2=2013,E2="NotSun"),"Both","`")
    G2    =COUNTIF($F$2:$F2,"Both")


However, the question now is:

    Can the same endpoint be achieved in a single column?

I have always assumed that any number of formulas—each of which builds upon the previous—can be nested into a single formula. However, that may be a very naïve assumption and not true. If it is true though, I cannot figure out how to put the formulas together in this case.

Thanks
2013-06-05a-EE-function-for-crit.xlsx
0
Comment
Question by:Steve_Brady
5 Comments
 
LVL 32

Assisted Solution

by:bhess1
bhess1 earned 150 total points
ID: 39224248
I believe you need at least two formulas.  One to generate your data, one to interpret it.

As I see it, the formula for generating the running total would be:

=IF(YEAR(A2)=2013,IF(TEXT(A2, "ddd")<>"Sun",IF(ISNUMBER(G1), G1+1, 1), IF(ISNUMBER(G1), G1, 0)), IF(ISNUMBER(G1), G1, 0))

Where A is the column with the date data, and G is the running total column.
0
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 150 total points
ID: 39224504
Hi,

Expanding on bhess1's suggestion, combining the test for the year (2013) & the day of the week (not Sunday) within the same initial IF condition:

Please paste this formula into cell [G2]:

=IF(AND(YEAR(A2)=2013,TEXT(A2,"ddd")<>"Sun"),IF(ISNUMBER(G1),G1,0)+1,IF(ISNUMBER(G1),G1,0))

Then copy cell [G2] to the Windows Clipboard & paste down column [G] to cell [G11] (i.e. [G3:H11]).

Also copy [G2] to [G15:G24] (or as far down column [G] as required).

(Please see the attached workbook)

BFN,

fp.
Q-28149033.xlsx
0
 
LVL 24

Assisted Solution

by:Steve
Steve earned 50 total points
ID: 39224805
Have you tried:

=SUM(N(YEAR(A$2:A2)=2013)*N(WEEKDAY(A$2:A2)<>1)) {cse}

See attached
2013-06-05a-EE-function-for-crit.xlsx
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 150 total points
ID: 39225142
Of course if you use * to multiply the two conditions then the Boolean co-ercion is done implicitly by * so you don't need the N functions or any replacement for them, so this works too

=SUM((YEAR(A$2:A2)=2013)*(WEEKDAY(A$2:A2)>1))

confirmed with CTRL+SHIFT+ENTER

.....but I prefer bhess1/fanpages' approach, for a running total why re-calculate all the previous rows each time, that's just wasted effort, you can simply ascertain whether each row meets the conditions and add that to the previous row total, e.g. with this formula in G2 copied down

=SUM((YEAR(A2)=2013)*(WEEKDAY(A2)>1),G1)

regards, barry
0
 

Author Closing Comment

by:Steve_Brady
ID: 39259827
Great responses. Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Office 2016 Excel Issue 4 27
Highlighting cells in Excel 9 17
Excel Automation VBA 19 38
NEED TO UPDATE DATA IN EXCEL 18 32
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now