Edit array constants by checkbox?

[morningglow16]

Is it possible to have a checked/unchecked box change an array constant in a formula? I am working on a day of the week formula, that based on checked boxes, would affect the amount of days used in the array constant formula.
Thanks.

 

[AlphaFrog]

This seemed to work.

I linked a Forms Control-type Checkbox to cell C1 and then used an IF function to select one of two array constants depending on the status of cell C1.

=SUM(IF(C1,{1,2,3},{10,20,30}))

This just sums one of the two array constants based on the Checkbox. The result is 6 if the Checkbox is checked and 60 if it is unchecked.

 

[morningglow16]

thanks for your response. I have played with your formula a bit, but am having a hard time tying it to mine. I am still an excel beginner, so I may not be able to see the full picture. Here is my formula as it stands (for 3 days mon-weds):

=IF(Sheet1!B26="",0,SUMPRODUCT(INT((MAX(D18,D17)-WEEKDAY(MAX(D18,D17)+1-{2;3;4})-
MIN(D18,D17)+8)/7)))

How would I insert that IF statement into this formula (and for all 7 days potentially) basically, IF Monday is checked then (2) if it is un-checked then (dont apply towards calculation) Thanks again for your help.

 

[AlphaFrog]

I don't follow what you are doing currently and what end result you ultimately want to calculate.

Also, if you described the "big picture" that would help. Your formula references two dates. What do you want to calculate from them? What is the purpose of the checkbox?

 

[morningglow16]

Sorry, I want to find out how many of the checked days of the week occur between two dates. Like how many mondays and tuesdays are between 1/1/11 = 1/31/11. The reason for the different days in the checkboxes are because not all employees work the same days of the week.
Thanks again.

 

[AlphaFrog]

Column B are the linked cells to 7 checkboxes (one for each weekday)

Excel Workbook
	A	B	C	D	E	F
1	Sunday	FALSE		Date Range		Weekday Count
2	Monday	TRUE		1/1/2011		9
3	Tuesday	FALSE		1/31/2011
4	Wednesday	TRUE
5	Thursday	FALSE
6	Friday	FALSE
7	Saturday	FALSE
Sheet

 

[morningglow16]

Thanks, somethings not working for me though. I keep coming back with 90 days between 1/1/11 and 1/30/11. Im trying to calculate monday-weds, it should be 12. Could i be copying it wrong?

 

[AlphaFrog]

The number of M-T-W in 1/1/2011 and 1/31/2011 is 13 not 12

Could i be copying it wrong?

I have no idea what you have done. It could be anything.

What is the range of cells that have your checkbox results (equivalent to B1:B7 above)?

The new formula calculates for all weekdays then nulls out the weekdays that are False. So the range B1:B7 has to be the same size as the array constant {1;2;3;4;5;6;7}

The checkbox range (B1:B7) has to be in one column and not one row to match the other arrays in the formula. It matters with SUMPRODUCT if all the referenced cells and array constants are in columns or rows. Can't easily mix and match column arrays with row arrays.

 

[morningglow16]

Thank you so much, it worked. I was putting it in a row instead of a column. Amazing a small change like that makes such a big difference. You are a true expert. I appreciate the help, alpha.

 

[AlphaFrog]

You're welcome. I'm glad it worked.

Note: you could put it in a row if you want, but then the array constant has to be separated with commas instead of semicolons to make it a horizontal array instead of a vertical array. The example below uses H2:N2 (instead of B1:B7) and note the array constant...

=SUMPRODUCT(INT((MAX(D2:D3)-WEEKDAY(MAX(D2:D3)+1-{1,2,3,4,5,6,7})-MIN(D2:D3)+8)/7)*H2:N2)