Todays date based on other cell value
- Thread starter sunnyp
- Start date
NARAYANK991
Excel Ninja
Hi ,
In B1 use the formula :
=IF(A1=1000,TODAY(),"No date specified")
You have not mentioned what should be displayed if A1 does not contain 1000.
Narayan
In B1 use the formula :
=IF(A1=1000,TODAY(),"No date specified")
You have not mentioned what should be displayed if A1 does not contain 1000.
Narayan
NARAYANK991
Excel Ninja
Hi ,
Inspite of my earlier post , there is no clarification !
Try this :
=IF(OR(A1={1000,2000,3000,4000}),TODAY(),"No date specified")
This is to be entered as an array formula , using CTRL SHIFT ENTER.
Formatting can be done on the cell where this formula is being entered , as if the format is included within the formula , the output will be TEXT , and cannot be used in calculations ; if you still want this , use this :
=IF(OR(A1={1000,2000,3000,4000}),TEXT(TODAY(),"mm/dd/yyyy"),"No date specified")
Again , this is to be entered as an array formula , using CTRL SHIFT ENTER.
Narayan
Inspite of my earlier post , there is no clarification !
Try this :
=IF(OR(A1={1000,2000,3000,4000}),TODAY(),"No date specified")
This is to be entered as an array formula , using CTRL SHIFT ENTER.
Formatting can be done on the cell where this formula is being entered , as if the format is included within the formula , the output will be TEXT , and cannot be used in calculations ; if you still want this , use this :
=IF(OR(A1={1000,2000,3000,4000}),TEXT(TODAY(),"mm/dd/yyyy"),"No date specified")
Again , this is to be entered as an array formula , using CTRL SHIFT ENTER.
Narayan
Somendra Misra
Excel Ninja
@NARAYANK991 Sir,
I think your formula doesn't require Ctrl+Shift+Enter. As array constant don't require it.
Regards,
I think your formula doesn't require Ctrl+Shift+Enter. As array constant don't require it.
Regards,
NARAYANK991
Excel Ninja
Hi Misra ,
Is it so ? Thanks. I didn't know it.
Narayan
Is it so ? Thanks. I didn't know it.
Narayan
Somendra Misra
Excel Ninja
Narayan Sir,
If the values would have been in cell range and it should have been like A1=D1:D4 than CSE is must.
Regards,
If the values would have been in cell range and it should have been like A1=D1:D4 than CSE is must.
Regards,
NARAYANK991
Excel Ninja
Hi Misra ,
I don't think we can draw a general conclusion that since it is an array constant , therefore CSE is not required ; suppose we have a list of numbers , and we wish to see how many of them are the numbers 1 or 3 i.e. how many times do the numbers 1 , 3 occur in the list.
We can use : =SUM(COUNTIF($A$7:$A$17,{1,3})) without CSE , whereas , if we have the numbers 1 and 3 in cells C1 and C2 , we will have to use =SUM(COUNTIF($A$7:$A$17,C1:C2)) with CSE.
However , we also can use the somewhat convoluted :
=SUM(--(ISNUMBER(MATCH($A$7:$A$17,{1,3},0))))
which will not give the correct result without CSE.
Narayan
I don't think we can draw a general conclusion that since it is an array constant , therefore CSE is not required ; suppose we have a list of numbers , and we wish to see how many of them are the numbers 1 or 3 i.e. how many times do the numbers 1 , 3 occur in the list.
We can use : =SUM(COUNTIF($A$7:$A$17,{1,3})) without CSE , whereas , if we have the numbers 1 and 3 in cells C1 and C2 , we will have to use =SUM(COUNTIF($A$7:$A$17,C1:C2)) with CSE.
However , we also can use the somewhat convoluted :
=SUM(--(ISNUMBER(MATCH($A$7:$A$17,{1,3},0))))
which will not give the correct result without CSE.
Narayan
Somendra Misra
Excel Ninja
That's great: so let me make a general statement.
In formulas where formula is expecting 1 value as k in LARGE OR SMALL COUNTIF criteria argument, there if you pass array constant such as {1,2,3} it will not require CSE else it will.
Regards,
In formulas where formula is expecting 1 value as k in LARGE OR SMALL COUNTIF criteria argument, there if you pass array constant such as {1,2,3} it will not require CSE else it will.
Regards,
NARAYANK991
Excel Ninja
Hi Misra ,
One point which needs to be found out is :
Does array-entering a formula which does not need to be array-entered make any difference to overall recalculation time ?
We know that if a formula which needs to be array-entered is not entered using CSE , it will not give the correct result ; so it is logical that if , and this is the IF which needs to be found out , array-entering a formula which does not need to be array-entered does not make any difference to overall recalculation time , then why bother ?
Narayan
One point which needs to be found out is :
Does array-entering a formula which does not need to be array-entered make any difference to overall recalculation time ?
We know that if a formula which needs to be array-entered is not entered using CSE , it will not give the correct result ; so it is logical that if , and this is the IF which needs to be found out , array-entering a formula which does not need to be array-entered does not make any difference to overall recalculation time , then why bother ?
Narayan
Thank you, it works with =IF(OR(A1={1000,2000,3000,4000}),TEXT(TODAY(),"mm/dd/yyyy") but i get FALSE when there is no data, would you please show how to get rid off this error?
XOR LX
Active Member
I agree that I'm not sure it's possible to make a general conclusion as to whether a given construction will require array-entry based solely upon the type of parameter being passed.
It appears that this property is also function-dependent: some are simply able to operate over an array of values without the need for CSE; others aren't.
Granted, that ability may vary depending on the type of array being passed (constant/worksheet reference), though that alone does not appear to be a sufficient measure as to be able to justify a logical relationship between type of array and need for CSE.
Regards
It appears that this property is also function-dependent: some are simply able to operate over an array of values without the need for CSE; others aren't.
Granted, that ability may vary depending on the type of array being passed (constant/worksheet reference), though that alone does not appear to be a sufficient measure as to be able to justify a logical relationship between type of array and need for CSE.
Regards
Somendra Misra
Excel Ninja
@XOR LX
But one thing is sure Excel is pretty smart to distinguish between an array constant and a worksheet range. Functions which accept array argument don't require CSE like SUMPRODUCT, INDEX(array.... and others...
Let say =LARGE(A1:A5,{1,2}) will not require CSE as but if you say =LARGE(A1:A5,B1:B2) where B1 = 1 and B2 = 2, it require CSE, so I think by CSE we tell EXCEL to do array calculation inside any function and conversion of the range into array constant is also treated like calculation step , so by passing array constant we eliminate that step so CSE is not required.
Same goes for =VLOOKUP(B9,B2:F5,H6:J6,0) returns an error with out CSE and =VLOOKUP(B9,B2:F5,{1,2,3},0) does not return error with out CSE.
Regards,
But one thing is sure Excel is pretty smart to distinguish between an array constant and a worksheet range. Functions which accept array argument don't require CSE like SUMPRODUCT, INDEX(array.... and others...
Let say =LARGE(A1:A5,{1,2}) will not require CSE as but if you say =LARGE(A1:A5,B1:B2) where B1 = 1 and B2 = 2, it require CSE, so I think by CSE we tell EXCEL to do array calculation inside any function and conversion of the range into array constant is also treated like calculation step , so by passing array constant we eliminate that step so CSE is not required.
Same goes for =VLOOKUP(B9,B2:F5,H6:J6,0) returns an error with out CSE and =VLOOKUP(B9,B2:F5,{1,2,3},0) does not return error with out CSE.
Regards,
Eloise T
Active Member
I did not see if the question was answered...
If you use CSE (Ctrl+Shift+Enter) on a formula that does not need it, there are no adverse effects, correct? The only adverse effect is when CSE is needed and NOT used?
ET
---------------------------------------------------------------------------------------------
“Perseverance is failing 19 times requesting an upgrade and succeeding anyway
using Excel 2007.” - Eloise T.
(Still using Excel 2007)
If you use CSE (Ctrl+Shift+Enter) on a formula that does not need it, there are no adverse effects, correct? The only adverse effect is when CSE is needed and NOT used?
ET
---------------------------------------------------------------------------------------------
“Perseverance is failing 19 times requesting an upgrade and succeeding anyway
using Excel 2007.” - Eloise T.
(Still using Excel 2007)
Last edited:
shrivallabha
Excel Ninja
I set up a sheet with following:
Column A to E contain 1,2,3,4,5 respectively.
In column F formula:
=SUM(A1:E1)
was applied throughout.
Then following code was used to check sheet re-calculation time.
Code:
Public Sub SomeTest()
t = Timer
Sheet1.Calculate
Debug.Print Timer - t
End SubWhen the formula was applied normally, re-calc time was 0.046875 and when it was CSE, re-calc time was 0.109375. So Excel uses different mechanism to evaluate CSE entered formulas and it is more resource intensive.
As to calculated results, they were correct (irrespective of the type of entry).
Eloise T
Active Member
Thank you. Good to know. So if you use CSE and don't need it, the only downside is a few microseconds lost and no calculation errors.
...and nice timer VBA!
ET
------------------------------------------------------------------------------------------
“Perseverance is failing 19 times requesting an upgrade and succeeding anyway using Excel 2007.” - Eloise T.
(Still using Excel 2007)
...and nice timer VBA!
ET
------------------------------------------------------------------------------------------
“Perseverance is failing 19 times requesting an upgrade and succeeding anyway using Excel 2007.” - Eloise T.
(Still using Excel 2007)
shrivallabha
Excel Ninja
I doubt any real life situation will be simpler like the one I've posted. My main intent was to show that it may add to calculation time and potentially slow down things.
In general, when we try to assess such things and go too far with the line of thinking we may clog our minds thinking as if it is everyday occurrence. I'd like to think that there will be very few cases of people doing CSE where it is not required. As opposed to that, higher will be the cases where people will miss CSE where it is required.
But what if the formula gives wrong results? So it is better not to generalize it as rule and be aware of effects and side-effects.