If condition and Date Ranges
Experts,
How could I modify Col G formula to return SAIBOR rate as shown on the "Set Libor Saibor" if the Facility is either SAR Procurement (R2) or SAR Wakala (R2) but if not either of those then reference the Libor rate.
One other condition is that I need to match on the date ranges as shown in Col P & Q.
Please see attached file.
Hope that isn't too complicated. :)
thank you.
EE_DSRA.xlsx
How could I modify Col G formula to return SAIBOR rate as shown on the "Set Libor Saibor" if the Facility is either SAR Procurement (R2) or SAR Wakala (R2) but if not either of those then reference the Libor rate.
One other condition is that I need to match on the date ranges as shown in Col P & Q.
Please see attached file.
Hope that isn't too complicated. :)
thank you.
EE_DSRA.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Shums... not at at a computer at the moment. I probably will not be able to test for a few hours. Thank you once again
ASKER
very nice. You are good. Wish I had your knowledge.
Quick question:
I keep getting this "Calculating" near bottom of file and whenever I make an update then it takes awhile for the file to update. I think it has to do with the formula in col H: {=IFERROR(INDEX(Margin!B:B
do you see any workaround? thanks again shums. If you do know of a workaround I can post another question.
Quick question:
I keep getting this "Calculating" near bottom of file and whenever I make an update then it takes awhile for the file to update. I think it has to do with the formula in col H: {=IFERROR(INDEX(Margin!B:B
do you see any workaround? thanks again shums. If you do know of a workaround I can post another question.
ASKER
what I am referring to:
it seems to delay quite awhile when I make a change to the file.
it seems to delay quite awhile when I make a change to the file.
Check the used range on your Margin sheet.
Select the sheet and Press End then Home. The cursor should go to the bottom of the data. If the cursor goes way beyond the data then the Used Range has got messed up.
Put the cursor in the row below the last row of data and then press Shift plus End then Home. That will highlight all the surplus rows, right click and choose Delete and select Entire Rows option and click OK.
Repeat the process for removing surplus columns. Put the cursor one column beyond your data and repeat key selections to highlight surplus columns. Right click, Delete, Entire Columns, OK.
Then Save the file. Saving should then reset the Used Range setting. Test by pressing End then Home and the cursor should now go to the bottom of the data.
Select the sheet and Press End then Home. The cursor should go to the bottom of the data. If the cursor goes way beyond the data then the Used Range has got messed up.
Put the cursor in the row below the last row of data and then press Shift plus End then Home. That will highlight all the surplus rows, right click and choose Delete and select Entire Rows option and click OK.
Repeat the process for removing surplus columns. Put the cursor one column beyond your data and repeat key selections to highlight surplus columns. Right click, Delete, Entire Columns, OK.
Then Save the file. Saving should then reset the Used Range setting. Test by pressing End then Home and the cursor should now go to the bottom of the data.
Cleaned up your extra spacing, please try attached and let me know...
pdvsa_EE_DSRA.xlsx
pdvsa_EE_DSRA.xlsx
ASKER
Hi Rob, thank you for that bit of advice. I have heard this prescription before although not certain how deleting the rows/coulumns affects the calculation. I feel confident it will reduce the calculation time though.
Thank you Shums. I will check out the file in the morning. I am about to go to sleep right now. Thank you guys once again and really appreciate your help.
Thank you Shums. I will check out the file in the morning. I am about to go to sleep right now. Thank you guys once again and really appreciate your help.
You're Welcome! Sleep tight :)
ASKER
:).
It helps the calculation time because when you refer to whole columns in a formula the workings behind the scenes knows to stop at the used range but if the used range is corrupted it actually uses the whole column which at over 1m rows will take some time, particularly when using array formulas.
ASKER
HI Shums, I saved the cleaned up file but it doesn't seem to reduce any calculation times at least on my end. I do see that the range is cleaned up though. You can test it by going to the LIbor/Saibor sheet and update a rate.
let me know if I am doing something wrong. thank you.
let me know if I am doing something wrong. thank you.
ASKER
HI Shums...I did make a change to the formula:
notice the $ signs. I don't get the "calculation" anylonger as mentioned but the formula doesn't work either. Do you see an issue with using the $ in that way?
=IF(OR([@Facility]="SAR Procurement (R2)",[@Facility]="SAR Wakala (R2)"),IFERROR(INDEX('Set Libor Saibor'!$B$1:$B$100,MATCH(
notice the $ signs. I don't get the "calculation" anylonger as mentioned but the formula doesn't work either. Do you see an issue with using the $ in that way?
=IF(OR([@Facility]="SAR Procurement (R2)",[@Facility]="SAR Wakala (R2)"),IFERROR(INDEX('Set Libor Saibor'!$B$1:$B$100,MATCH(
in which sheet?