If function that enters values in two cells
Dear all,
I wonder if this is feasible:
An "If" function, where not only the formula cell gets an entry but another cell also at the same time.
I have attached a sample file for your convenience.
Help is much apprecitated. Thank you very much in advance.
Regards, Andreas
Formula.xls
I wonder if this is feasible:
An "If" function, where not only the formula cell gets an entry but another cell also at the same time.
I have attached a sample file for your convenience.
Help is much apprecitated. Thank you very much in advance.
Regards, Andreas
Formula.xls
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or as Barry says, havign formula in both B and C!
ASKER
Great, thank you very much for your quick support.
Ok, that is what I also thought. I might get back and ask for a macro solution.
Kind regards, Andreas
Ok, that is what I also thought. I might get back and ask for a macro solution.
Kind regards, Andreas
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I meant to attach this sample workbook showing the array formula entered in cells B3 and C3.
FormulaQ26825283.xls
FormulaQ26825283.xls
ASKER
Brad,
oops, I was maybe a millisecond quicker with my feedback. Ok, I will give this a try and let you know. I will not get back to you before tomorrow.
thank you very much.
Regards, Andreas
oops, I was maybe a millisecond quicker with my feedback. Ok, I will give this a try and let you know. I will not get back to you before tomorrow.
thank you very much.
Regards, Andreas
Andreas, this goes back to Barry's suggestion of you entering formula in two cells!
ASKER
Hi Brad,
will need the weekend to try it out. Will get back with a feedback afterwards. Thank you again to all for the overwhelming support.
Regards, Andreas
will need the weekend to try it out. Will get back with a feedback afterwards. Thank you again to all for the overwhelming support.
Regards, Andreas
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Brad,
this is great stuff. It is working just fine. I awarded 50 points to Barry as well because he was the quickest to answer with essentially another right answer. But I really like your approach. This forum really deserves its name.
Regards and thank you again for your professional help.
Andreas.
this is great stuff. It is working just fine. I awarded 50 points to Barry as well because he was the quickest to answer with essentially another right answer. But I really like your approach. This forum really deserves its name.
Regards and thank you again for your professional help.
Andreas.
Andreas,
In the simple example you posted in the question, most people would be quite satisfied with two regular formulas.
Where the array entry and return of values comes into play is when you have a function or calculation that really needs to return multiple values, such as LINEST or a user-defined function returning an array. It is in these latter cases that there can be a considerable savings in time. To illustrate, in the snowflake counting problem described in my screen profile, I originally returned values one at a time from user-defined functions. Switching to return of an entire array cut the recalculation time from about five minutes to a couple of seconds. This was because the results needed to be calculated just once by the user-defined function, rather than once for each cell. Since I was returning results to 13 column s x 24 rows (312 cells) using a VBA user-defined function, you can see the benefits to returning all the values after performing a single calculation.
Brad
In the simple example you posted in the question, most people would be quite satisfied with two regular formulas.
Where the array entry and return of values comes into play is when you have a function or calculation that really needs to return multiple values, such as LINEST or a user-defined function returning an array. It is in these latter cases that there can be a considerable savings in time. To illustrate, in the snowflake counting problem described in my screen profile, I originally returned values one at a time from user-defined functions. Switching to return of an entire array cut the recalculation time from about five minutes to a couple of seconds. This was because the results needed to be calculated just once by the user-defined function, rather than once for each cell. Since I was returning results to 13 column s x 24 rows (312 cells) using a VBA user-defined function, you can see the benefits to returning all the values after performing a single calculation.
Brad
ASKER
Hi Brad,
thank you very much for your insight into this professional matter. This array formula really can come in handy for me. It is incredible how this array formula can cut computing time so considerably in certain circumstances.
Regards, Andreas
thank you very much for your insight into this professional matter. This array formula really can come in handy for me. It is incredible how this array formula can cut computing time so considerably in certain circumstances.
Regards, Andreas
you would only be able to do this is you ran a macro.