Steve_Brady
asked on
Need help combining 4 Excel formulas please
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:
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
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:
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER