LinkBack URL
About LinkBacks
Hi,
I would like help with two formulas. Please see attachment.
Help1: one cell to calculate only on Fridays
Help2: one cell to reference other cells only on Fridays
Thank so much!
Ask Excel forum - ASabur.xlsx
Hi,
I would like help with two formulas. Please see attachment.
Help1: one cell to calculate only on Fridays
Help2: one cell to reference other cells only on Fridays
Thank so much!
Ask Excel forum - ASabur.xlsx
Please mock up the output.
Quang PT
Maybe on sheet "Help 1", cell C4:
=IF(WEEKDAY(TODAY(),15)=1,SUM(B4,G5)-G4,0)
G4:
=IF(WEEKDAY(TODAY(),15)=1,'Help 2'!F9,0)
on sheet Help 2", cell A9:
=IF(WEEKDAY(TODAY(),15)=1,
IF(WEEKDAY(A1,15)=1,A2,0)+
IF(WEEKDAY(C1,15)=1,C2,0)+
IF(WEEKDAY(E1,15)=1,E2,0)+
IF(WEEKDAY(G2,15)=1,G2,0),0)
Can you explain what you mean by mock up the output?Originally Posted by bebo021999
Help 2 F9:
=IF(WEEKDAY(TODAY(),15)=1,XLOOKUP(TODAY(),A1:G1,A2:G2),"")
Help 1 C4:
=IF(WEEKDAY(TODAY(),15)=1,SUM(B4,G5)-G4,"")
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thanks AliGW!
Your formula returned no value and I realized its because I am checking it today (Saturday). I apologize, I should have realized that I need both values to remain until the following Friday.
Please see new attachment.Ask Excel forum (rev,1) - ASabur.xlsx
Last edited by asabur6; 06-08-2024 at 01:11 PM.
Thanks bebo021999!
Your formula returned no value and I realized its because I am checking it today (Saturday). I apologize, I should have realized that I need both values to remain until the following Friday.
Please see new attachment.Ask Excel forum (rev,1) - ASabur.xlsx
Try this in F9,
=XLOOKUP(TODAY()+7-WEEKDAY(TODAY()+1),A1:G1,A2:G2,,-1)
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
Thanks Haseeb,
But yours returned 4. 4 shouldnt show until June 14.
I need to return 3 and remain 3 until June 14, then return 4, and so on.
see picexcel forum pic - Asabur.JPG
Sorry about that. I mistakenly thought next Friday. This should work.
=XLOOKUP(TODAY()+1-WEEKDAY(TODAY(),15),A1:G1,A2:G2,,-1)
Thanks Haseeb, that worked for sheet Help 2. Can you help me with the formula on Help 1 cell C4? same attachment.
I am confused about Help 1 C4. According to your notes on the sheet, the formula provided in post #5 should work. Please explain if you are looking something else.
The formula she provided for Help 1, C4, did work but I should have clarified that I need the output to remain until the following Friday, just like Help 2 F9 (now F15) does.
Also, can you include additional cells to Help 2 F15 formula?
Ask Excel forum (rev.2) - ASabur.xlsx
Thanks!
Can you redesign your sheet for data entry?
the way you are entering is giving unnecessary calculation to excel. Attached is a one recommended method for better entry as well as calculation.
In a new sheet, one column use for entering date & second column for entering it's values. Then convert them to table format.
Then in a new sheet you can simply use a formula to get desired result very easily.
E2, for current date
=TODAY()
E3, to get current Friday date.
=E2+1-WEEKDAY(E2,15)
Then you can simply use the following formula to get the value.
=SUM(TEXTAFTER(FILTER(Table1[Value],Table1[Date]=E3),"-")+0)
Hi Haseeb,
My sheets are mockups of two of my actual sheets. My actual data is entered on Help2 under each date, in the format of (e.g. Billname - 16.36). I have a list of bills due under each date and total at the top. So I cant have it listed as you suggested in your New Method Entry sheet.
The current calculations are working, I just need the following tweeks:
Help1 C4 - allow to only calculate every Friday and remain until the next Friday. (i.e. on June 7 show total until June 14, then show new total, and so on.)
Help2 F15 - just include the additional cells A8:G7 & A8:G8 to reference in the formula =XLOOKUP(TODAY()+1-WEEKDAY(TODAY(),15),A1:G1,A2:G2,,-1)
Thanks!
Try the following array formula** in cell F15 on the Help2 sheet:Formula:
Please Login or Register to view this content.
For the 365 version of Excel I believe that you can activate by just pressing the Enter key.
For earlier versions of Excel you may have to follow these instructions: **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thank you JeteMc!
That formula worked for my array. I no longer need a formula for Help1 C4.
Thank you everyone!
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks