Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Adaptation Of A Formula
I have the following formula in a cell.
This gives me the number of how many Bank Holidays have passed in this tax year. =HLOOKUP(TODAY(),{0,38474,38502,38593,38712,38713, 38719;0,1,2,3,4,5,6},2) (courtesy of N. Harkawat) In any one tax year there can be up to 10 Bank Holidays. (This year there are only 6) I would like the cell to read "1 of 6", "2 of 6" etc, but in an automated way. On a separate sheet, I could put in A1 to J1 for instance, the dates of the Bank Holidays in the current tax year. In cells B1 to J1 I could have the numbers 1 to 10. (Or any other way or advice greatly appreciated) I simply want to enter the dates of the Bank Holidays in the A row and have the main cell (Holidays!$P$6) read "1 of 6" this year or "1 of 8" next year. Hope you can understand this logic. Thanking you in anticipation. -- Big Rick |
#2
|
|||
|
|||
Assign the name "Holidays" to the list (Ctrl-F3) and use this formula:
=MATCH(TODAY(),Holidays)-1&" of "&COUNT(Holidays) The list should start with 0. The name should be 11 rows long (the first is zero, eg A1:A11) even if you have fewer holidays in a given year and the extra cells are empty. -- Jim "Big Rick" wrote in message ... |I have the following formula in a cell. | This gives me the number of how many Bank Holidays have passed in this tax | year. | =HLOOKUP(TODAY(),{0,38474,38502,38593,38712,38713, 38719;0,1,2,3,4,5,6},2) | (courtesy of N. Harkawat) | | In any one tax year there can be up to 10 Bank Holidays. (This year there | are only 6) I would like the cell to read "1 of 6", "2 of 6" etc, but in an | automated way. | | On a separate sheet, I could put in A1 to J1 for instance, the dates of the | Bank Holidays in the current tax year. In cells B1 to J1 I could have the | numbers 1 to 10. | (Or any other way or advice greatly appreciated) | | I simply want to enter the dates of the Bank Holidays in the A row and have | the main cell (Holidays!$P$6) read "1 of 6" this year or "1 of 8" next year. | Hope you can understand this logic. | | | Thanking you in anticipation. | -- | Big Rick |
#3
|
|||
|
|||
One more if you only put one year's worth of data in sheet2.
=COUNTIF(Sheet2!A1:A11,"<="&TODAY()) &" of " & COUNT(sheet2!A1:A11) If you kept adding dates to that list (keeping the old for historical reasons and adding future for ease of updates????), you could use something like: =SUMPRODUCT(--(YEAR(sheet2!A1:A1000)=YEAR(today())), --(sheet2!A1:A1000<=today())) &" of "&SUMPRODUCT(--(YEAR(sheet2!A1:A1000)=YEAR(today()))) (all one cell) Big Rick wrote: I have the following formula in a cell. This gives me the number of how many Bank Holidays have passed in this tax year. =HLOOKUP(TODAY(),{0,38474,38502,38593,38712,38713, 38719;0,1,2,3,4,5,6},2) (courtesy of N. Harkawat) In any one tax year there can be up to 10 Bank Holidays. (This year there are only 6) I would like the cell to read "1 of 6", "2 of 6" etc, but in an automated way. On a separate sheet, I could put in A1 to J1 for instance, the dates of the Bank Holidays in the current tax year. In cells B1 to J1 I could have the numbers 1 to 10. (Or any other way or advice greatly appreciated) I simply want to enter the dates of the Bank Holidays in the A row and have the main cell (Holidays!$P$6) read "1 of 6" this year or "1 of 8" next year. Hope you can understand this logic. Thanking you in anticipation. -- Big Rick -- Dave Peterson |
#4
|
|||
|
|||
Try using the roating prismatic condenser formula in your toolbar. Via the
electro hydro thermo on a non varieable reastat eg. =HLOOKUP(TODAY(),{0,38474,38502,38593,38712,38713, 38719;0,1,2,3,4,5,6},&/10%34"12"$11/40) lucy x "Big Rick" wrote: I have the following formula in a cell. This gives me the number of how many Bank Holidays have passed in this tax year. =HLOOKUP(TODAY(),{0,38474,38502,38593,38712,38713, 38719;0,1,2,3,4,5,6},2) (courtesy of N. Harkawat) In any one tax year there can be up to 10 Bank Holidays. (This year there are only 6) I would like the cell to read "1 of 6", "2 of 6" etc, but in an automated way. On a separate sheet, I could put in A1 to J1 for instance, the dates of the Bank Holidays in the current tax year. In cells B1 to J1 I could have the numbers 1 to 10. (Or any other way or advice greatly appreciated) I simply want to enter the dates of the Bank Holidays in the A row and have the main cell (Holidays!$P$6) read "1 of 6" this year or "1 of 8" next year. Hope you can understand this logic. Thanking you in anticipation. -- Big Rick |
#5
|
|||
|
|||
Many thanks to you both. I have used the first of Daves formula.
To Jim. Yours didnt quite work out. I think it was counting the 0 as an actual holiday and was giving me 3 out of 7 instead of 3 out of 6. I truly appreciate your time and effort from you both. Dear Lucy. Am I supposed to laugh ? -- Big Rick "Dave Peterson" wrote: One more if you only put one year's worth of data in sheet2. =COUNTIF(Sheet2!A1:A11,"<="&TODAY()) &" of " & COUNT(sheet2!A1:A11) If you kept adding dates to that list (keeping the old for historical reasons and adding future for ease of updates????), you could use something like: =SUMPRODUCT(--(YEAR(sheet2!A1:A1000)=YEAR(today())), --(sheet2!A1:A1000<=today())) &" of "&SUMPRODUCT(--(YEAR(sheet2!A1:A1000)=YEAR(today()))) (all one cell) Big Rick wrote: I have the following formula in a cell. This gives me the number of how many Bank Holidays have passed in this tax year. =HLOOKUP(TODAY(),{0,38474,38502,38593,38712,38713, 38719;0,1,2,3,4,5,6},2) (courtesy of N. Harkawat) In any one tax year there can be up to 10 Bank Holidays. (This year there are only 6) I would like the cell to read "1 of 6", "2 of 6" etc, but in an automated way. On a separate sheet, I could put in A1 to J1 for instance, the dates of the Bank Holidays in the current tax year. In cells B1 to J1 I could have the numbers 1 to 10. (Or any other way or advice greatly appreciated) I simply want to enter the dates of the Bank Holidays in the A row and have the main cell (Holidays!$P$6) read "1 of 6" this year or "1 of 8" next year. Hope you can understand this logic. Thanking you in anticipation. -- Big Rick -- Dave Peterson |
#6
|
|||
|
|||
I meant:
=MATCH(TODAY(),Holidays)-1&" of "&COUNT(Holidays)-1 -- Jim "Big Rick" wrote in message ... | Many thanks to you both. I have used the first of Daves formula. | To Jim. Yours didnt quite work out. I think it was counting the 0 as an | actual holiday and was giving me 3 out of 7 instead of 3 out of 6. | I truly appreciate your time and effort from you both. | | Dear Lucy. | Am I supposed to laugh ? | -- | Big Rick | | | "Dave Peterson" wrote: | | One more if you only put one year's worth of data in sheet2. | | =COUNTIF(Sheet2!A1:A11,"<="&TODAY()) &" of " & COUNT(sheet2!A1:A11) | | If you kept adding dates to that list (keeping the old for historical reasons | and adding future for ease of updates????), you could use something like: | | =SUMPRODUCT(--(YEAR(sheet2!A1:A1000)=YEAR(today())), | --(sheet2!A1:A1000<=today())) | &" of "&SUMPRODUCT(--(YEAR(sheet2!A1:A1000)=YEAR(today()))) | | (all one cell) | | | Big Rick wrote: | | I have the following formula in a cell. | This gives me the number of how many Bank Holidays have passed in this tax | year. | =HLOOKUP(TODAY(),{0,38474,38502,38593,38712,38713, 38719;0,1,2,3,4,5,6},2) | (courtesy of N. Harkawat) | | In any one tax year there can be up to 10 Bank Holidays. (This year there | are only 6) I would like the cell to read "1 of 6", "2 of 6" etc, but in an | automated way. | | On a separate sheet, I could put in A1 to J1 for instance, the dates of the | Bank Holidays in the current tax year. In cells B1 to J1 I could have the | numbers 1 to 10. | (Or any other way or advice greatly appreciated) | | I simply want to enter the dates of the Bank Holidays in the A row and have | the main cell (Holidays!$P$6) read "1 of 6" this year or "1 of 8" next year. | Hope you can understand this logic. | | Thanking you in anticipation. | -- | Big Rick | | -- | | Dave Peterson | |
#7
|
|||
|
|||
Many thanks. I should of worked that one out for myself.
I am forever in your debt. -- Big Rick "Jim Rech" wrote: I meant: =MATCH(TODAY(),Holidays)-1&" of "&COUNT(Holidays)-1 -- Jim "Big Rick" wrote in message ... | Many thanks to you both. I have used the first of Daves formula. | To Jim. Yours didnt quite work out. I think it was counting the 0 as an | actual holiday and was giving me 3 out of 7 instead of 3 out of 6. | I truly appreciate your time and effort from you both. | | Dear Lucy. | Am I supposed to laugh ? | -- | Big Rick | | | "Dave Peterson" wrote: | | One more if you only put one year's worth of data in sheet2. | | =COUNTIF(Sheet2!A1:A11,"<="&TODAY()) &" of " & COUNT(sheet2!A1:A11) | | If you kept adding dates to that list (keeping the old for historical reasons | and adding future for ease of updates????), you could use something like: | | =SUMPRODUCT(--(YEAR(sheet2!A1:A1000)=YEAR(today())), | --(sheet2!A1:A1000<=today())) | &" of "&SUMPRODUCT(--(YEAR(sheet2!A1:A1000)=YEAR(today()))) | | (all one cell) | | | Big Rick wrote: | | I have the following formula in a cell. | This gives me the number of how many Bank Holidays have passed in this tax | year. | =HLOOKUP(TODAY(),{0,38474,38502,38593,38712,38713, 38719;0,1,2,3,4,5,6},2) | (courtesy of N. Harkawat) | | In any one tax year there can be up to 10 Bank Holidays. (This year there | are only 6) I would like the cell to read "1 of 6", "2 of 6" etc, but in an | automated way. | | On a separate sheet, I could put in A1 to J1 for instance, the dates of the | Bank Holidays in the current tax year. In cells B1 to J1 I could have the | numbers 1 to 10. | (Or any other way or advice greatly appreciated) | | I simply want to enter the dates of the Bank Holidays in the A row and have | the main cell (Holidays!$P$6) read "1 of 6" this year or "1 of 8" next year. | Hope you can understand this logic. | | Thanking you in anticipation. | -- | Big Rick | | -- | | Dave Peterson | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
writing a formula for a colored value | New Users to Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |