JohnTester
New Member
- Joined
- Sep 25, 2018
- Messages
- 9
Hey Excel Gurus.
I have been spending the past few days playing around with different formulas without success. Even know-it-all Google and "we have a video for that" Youtube cannot help.
Below is the XL2BB of what the spreadsheet looks like on Excel 365.
In Cell C it uses a Sum to see if the total value of the range is 4 or more, then the countdown from 24 to 0 days starts.
Once the eggs hatch(Cell D) the formula should zero out and the range for the Sum must start from the cell in C below the D cell containing the number of eggs hatched.
I have Tried INDEX, MATCH, SUMIF, AND, OR, DSUM and and and without success.
Please, please help!
I don't care if it's formula or VBA at this point.
I have been spending the past few days playing around with different formulas without success. Even know-it-all Google and "we have a video for that" Youtube cannot help.
Below is the XL2BB of what the spreadsheet looks like on Excel 365.
In Cell C it uses a Sum to see if the total value of the range is 4 or more, then the countdown from 24 to 0 days starts.
Once the eggs hatch(Cell D) the formula should zero out and the range for the Sum must start from the cell in C below the D cell containing the number of eggs hatched.
I have Tried INDEX, MATCH, SUMIF, AND, OR, DSUM and and and without success.
Please, please help!
I don't care if it's formula or VBA at this point.
2. February Breeding.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
2 | DATE | Amount of Eggs | Total Babies | Days to go | Days passed | |||
3 | ||||||||
4 | ||||||||
5 | Mon-31-Jan-22 | 0 | 0 | 0 | 0 | |||
6 | Tue-01-Feb-22 | 0 | 0 | |||||
7 | Wed-02-Feb-22 | 4 | 24 | 1 | ||||
8 | Thu-03-Feb-22 | 23 | 2 | |||||
9 | Fri-04-Feb-22 | 22 | 3 | |||||
10 | Sat-05-Feb-22 | 21 | 4 | |||||
11 | Sun-06-Feb-22 | 20 | 5 | |||||
12 | Mon-07-Feb-22 | 19 | 6 | |||||
13 | Tue-08-Feb-22 | 18 | 7 | |||||
14 | Wed-09-Feb-22 | 17 | 8 | |||||
15 | Thu-10-Feb-22 | 16 | 9 | |||||
16 | Fri-11-Feb-22 | 15 | 10 | |||||
17 | Sat-12-Feb-22 | 14 | 11 | |||||
18 | Sun-13-Feb-22 | 13 | 12 | |||||
19 | Mon-14-Feb-22 | 12 | 13 | |||||
20 | Tue-15-Feb-22 | 11 | 14 | |||||
21 | Wed-16-Feb-22 | 10 | 15 | |||||
22 | Thu-17-Feb-22 | 9 | 16 | |||||
23 | Fri-18-Feb-22 | 2 | 0 | 0 | ||||
24 | Sat-19-Feb-22 | 0 | 0 | |||||
25 | Sun-20-Feb-22 | 0 | 0 | |||||
26 | Mon-21-Feb-22 | 0 | 0 | |||||
27 | Tue-22-Feb-22 | 0 | 0 | |||||
28 | Wed-23-Feb-22 | 0 | 0 | |||||
29 | Thu-24-Feb-22 | 0 | 0 | |||||
30 | Fri-25-Feb-22 | 0 | 0 | |||||
31 | Sat-26-Feb-22 | 0 | 0 | |||||
32 | Sun-27-Feb-22 | 0 | 0 | |||||
33 | Mon-28-Feb-22 | 0 | 0 | |||||
34 | Tue-01-Mar-22 | 4 | 24 | 1 | ||||
35 | Wed-02-Mar-22 | 23 | 2 | |||||
36 | Thu-03-Mar-22 | 22 | 3 | |||||
37 | 8 | 2 | ||||||
Hok1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C5 | C5 | =--IF('C:\Users\HP Elite\Desktop\Work\Leonard\Birds\[1. January Breeding.xlsx]Hok1'!$F$36=0,0,'C:\Users\HP Elite\Desktop\Work\Leonard\Birds\[1. January Breeding.xlsx]Hok1'!$C$37) |
D5 | D5 | ='C:\Users\HP Elite\Desktop\Work\Leonard\Birds\[1. January Breeding.xlsx]Hok1'!$D$37 |
E5 | E5 | =IF(E6-1<=0,0,E6+1) |
F5 | F5 | =--IF('C:\Users\HP Elite\Desktop\Work\Leonard\Birds\[1. January Breeding.xlsx]Hok1'!$F$36<=0,0,'C:\Users\HP Elite\Desktop\Work\Leonard\Birds\[1. January Breeding.xlsx]Hok1'!$F$37) |
E6 | E6 | =IF(AND($C$5>=4,$F$5=24),IF(SUM($D$5:D6)=0,IF(SUM($C$6:C6)>=4,$H$1-F5,0),0),IF(SUM($D$5:D6)=0,IF(SUM($C$5:C6)>=4,$H$1-F5,0),0)) |
F6:F33,F35:F36 | F6 | =IF(SUM($F$5:F5)>299,0,IF(E6>0,IF(SUM($C$5:C6)>=4,1+F5),0)) |
E7:E33 | E7 | =IF(SUM($E$5:E6)>=299,0,IF(AND($C$5>=4,$F$5=24),IF(SUM($D$5:D7)=0,IF(SUM($C$6:C7)>=4,$H$1-F6,0),0),IF(SUM($D$5:D7)=0,IF(SUM($C$5:C7)>=4,$H$1-F6,0),0))) |
A5 | A5 | ='Over View'!C42-1 |
A6 | A6 | ='Over View'!C42 |
A7:A36 | A7 | =A6+1 |
C37:D37 | C37 | =SUM(C6:C36) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A33:G33 | Expression | =OR(WEEKDAY($A$5)=1,WEEKDAY($A$5)=7) | text | NO |
A34:G34 | Expression | =OR(WEEKDAY($A$6)=1,WEEKDAY($A$6)=7) | text | NO |
A35:G35 | Expression | =OR(WEEKDAY($A$7)=1,WEEKDAY($A$7)=7) | text | NO |
A36:G36 | Expression | =OR(WEEKDAY($A$8)=1,WEEKDAY($A$8)=7) | text | NO |
A34:G34 | Expression | =OR(WEEKDAY($A$13)=1,WEEKDAY($A$13)=7) | text | NO |
A32:G32 | Expression | =OR(WEEKDAY($A$19)=1,WEEKDAY($A$19)=7) | text | NO |
A32:G32 | Expression | =OR(WEEKDAY($A$18)=1,WEEKDAY($A$18)=7) | text | NO |
A31:G31 | Expression | =OR(WEEKDAY($A$17)=1,WEEKDAY($A$17)=7) | text | NO |
A30:G30 | Expression | =OR(WEEKDAY($A$16)=1,WEEKDAY($A$16)=7) | text | NO |
A29:G29 | Expression | =OR(WEEKDAY($A$15)=1,WEEKDAY($A$15)=7) | text | NO |
A28:G28 | Expression | =OR(WEEKDAY($A$14)=1,WEEKDAY($A$14)=7) | text | NO |
A27:G27 | Expression | =OR(WEEKDAY($A$13)=1,WEEKDAY($A$13)=7) | text | NO |
A26:G26 | Expression | =OR(WEEKDAY($A$19)=1,WEEKDAY($A$19)=7) | text | NO |
A25:G25 | Expression | =OR(WEEKDAY($A$18)=1,WEEKDAY($A$18)=7) | text | NO |
A24:G24 | Expression | =OR(WEEKDAY($A$17)=1,WEEKDAY($A$17)=7) | text | NO |
A23:G23 | Expression | =OR(WEEKDAY($A$16)=1,WEEKDAY($A$16)=7) | text | NO |
A22:G22 | Expression | =OR(WEEKDAY($A$15)=1,WEEKDAY($A$15)=7) | text | NO |
A21:G21 | Expression | =OR(WEEKDAY($A$14)=1,WEEKDAY($A$14)=7) | text | NO |
A20:G20 | Expression | =OR(WEEKDAY($A$20)=1,WEEKDAY($A$20)=7) | text | NO |
A19:G19 | Expression | =OR(WEEKDAY($A$19)=1,WEEKDAY($A$19)=7) | text | NO |
A18:G18 | Expression | =OR(WEEKDAY($A$18)=1,WEEKDAY($A$18)=7) | text | NO |
A17:G17 | Expression | =OR(WEEKDAY($A$17)=1,WEEKDAY($A$17)=7) | text | NO |
A16:G16 | Expression | =OR(WEEKDAY($A$16)=1,WEEKDAY($A$16)=7) | text | NO |
A15:G15 | Expression | =OR(WEEKDAY($A$15)=1,WEEKDAY($A$15)=7) | text | NO |
A14:G14 | Expression | =OR(WEEKDAY($A$14)=1,WEEKDAY($A$14)=7) | text | NO |
A13:G13 | Expression | =OR(WEEKDAY($A$13)=1,WEEKDAY($A$13)=7) | text | NO |
A12:G12 | Expression | =OR(WEEKDAY($A$12)=1,WEEKDAY($A$12)=7) | text | NO |
A11:G11 | Expression | =OR(WEEKDAY($A$11)=1,WEEKDAY($A$11)=7) | text | NO |
A10:G10 | Expression | =OR(WEEKDAY($A$10)=1,WEEKDAY($A$10)=7) | text | NO |
A9:G9 | Expression | =OR(WEEKDAY($A$9)=1,WEEKDAY($A$9)=7) | text | NO |
A8:G8,E7:F7 | Expression | =OR(WEEKDAY($A$9)=1,WEEKDAY($A$9)=7) | text | NO |
A7:D7,G7 | Expression | =OR(WEEKDAY($A$7)=1,WEEKDAY($A$7)=7) | text | NO |
A6:G6 | Expression | =OR(WEEKDAY($A$6)=1,WEEKDAY($A$6)=7) | text | NO |