• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with the IF function

K

Kevin

New Member
Hi all,

Am pretty new to Excel and I have a query.

I have a column of currencies(EUR, GBP & USD) and I have 3 rates of return for each currency( say they are in cells d36, e36 & f36. How do I make cell g3 look at a3 and display relevant return(Eur) so in this case it should display d36?

I think I've worded this right, apologies if I have been unclear. I'm probably trying to use the wrong function.

Any help gratefully received
Thanks
Kev
 

Attachments

Array formulas are notably slow and clumsy. A simple SUMPRODUCT does the job =SUMPRODUCT(($D$36:$F$36)*(LEFT($D$35:$F$35,)=A3))
 
I changed d35 to EUR
and then used the following in B3
=CHOOSE(MATCH(A3;{"EUR";"GBP";"USD"};0);$D$36;$E$36;$F$36)
 
Hi Shri..

If I says..

SUMPRODUCT is not an array Fromula.. It never gives multiple Output..
If some internal calculation, which is returning multiple output, to create a array for Sumproduct.. only in those case Sumproduct required CSE. but final output never gives multiple output..
Click to expand...

will I be wrong!!!:confused:
 
Hi Deb,

Let me put it this way. "CTRL+ENTER" part is handling Excel's way of understanding that data is being entered in more than one cell. That is array of cells or we can call it array entry. Obviously we can do this for a single cell also. In this case, the array contains one cell only.

Simple example would be: Selecting a range like A2:A10 and then typing some input and pressing the CTRL+ENTER together results in the same data being entered in the complete selection.The addition of CTRL key is for informing Excel of the changed behavior we expect from Excel unlike normal data that we fill by pressing "ENTER".

Now coming to formulas, when we do CTRL+ENTER excel updates references. If you try it on a formula without SHIFT key. But with addition of SHIFT key, Excel performs exactly the same action for each cell for the selected ARRAY. This is our interaction with Excel from input perspective (at least for me).

When I said ARRAY it was from not inputting perspective.

And here's is a link to a useful forum where Bob Philips had posted good tutorials.
http://www.vbaexpress.com/forum/showthread.php?26197-This-Forum

I started with VBAX, the spent some time on MrExcel and now I frequent Chandoo ;)
 
You must log in or register to reply here.
Back
Top