Excel Magic Trick 703

I am sure you are familiar with the excellent Excel Magic Trick series on YouTube. My question revolves around video #703 where multiple rows of data are created based upon a set of criteria which can be changed – thereby altering the resultant list.

I was able to get the count cell fine and even the first column correct, but when i tried to drag the result table formula from left to right, the data just repeated itself and did not bring with it the data under the adjacent column categories.

This was the formual for Transactions (the left-most column; Cell B18): 

=IF(ROWS(B$18:B18)>$B$16,””,INDEX($B$30:$B$39,AGGREGATE(15,6,(ROW($B$30:$B$39)-ROW($B$30)+1)/(($B$30:$B$39>=$B$13)*($C$30:$C$39>=$D$13)*($D$30:$D$39>=$E$13)*($E$30:$E$39>=$F$13)),ROWS(B$18:B18))))…the result was 816

Per the video, all I needed to do was click/drag that formula across from left to right to get my other data (Variance % [Column C], Returns $ [Column D], CB % [Column E]), but that all had the same value as the first cell in that row – 816.

Here is the formula for the Cell C18:

=IF(ROWS(C$18:C18)>$B$16,””,INDEX($B$30:$B$39,AGGREGATE(15,6,(ROW($B$30:$B$39)-ROW($B$30)+1)/(($B$30:$B$39>=$B$13)*($C$30:$C$39>=$D$13)*($D$30:$D$39>=$E$13)*($E$30:$E$39>=$F$13)),ROWS(C$18:C18))))

Per the video, I should be getting a percentage greater than 100% (references Cell D13), and so on.  What am I doing wrong if my formula is exactly how it is shown in the video? I am using Excel 2016 on Windows 365, but Excel 2010 should not be different, formula-wise.

Excel Magic Trick #703 video: https://www.youtube.com/watch?v=9jmNEqrfrZs

Thanks!

By: Kevin Francis

2,095 thoughts on “Excel Magic Trick 703

  1. healthy fresh recipes

    Hi there, There’s no doubt that your blog may be having browser compatibility problems. When I look at your website in Safari, it looks fine but when opening in I.E., it has some overlapping issues. I simply wanted to provide you with a quick heads up! Other than that, excellent website.

  2. udfauzi.com

    It’s nearly impossible to find experienced people in this particular topic, however, you sound like you know what you’re talking about! Thanks

  3. harga-jual.com

    Everything is very open with a very clear clarification of the issues. It was definitely informative. Your website is very helpful. Thanks for sharing!

  4. udfauzi.com

    After checking out a number of the blog articles on your website, I truly appreciate your technique of writing a blog. I bookmarked it to my bookmark webpage list and will be checking back in the near future. Please check out my website as well and tell me what you think.

  5. poboleda.org

    Hi there! I just would like to give you a huge thumbs up for the great info you’ve got right here on this post. I will be coming back to your site for more soon.

  6. camping

    Having read this I thought it was extremely informative. I appreciate you spending some time and energy to put this information together. I once again find myself spending a significant amount of time both reading and posting comments. But so what, it was still worth it!

  7. natural eyelash serum

    After checking out a few of the articles on your website, I truly appreciate your way of blogging.
    I saved as a favorite it to my bookmark site list and will be checking back
    soon. Please visit my web site too and tell me
    your opinion.

  8. download aplikasi vivo slot

    Oh my goodness! Impressive article dude! Thank you so much, However I am going through problems with your RSS. I don’t understand why I cannot join it. Is there anybody getting similar RSS problems? Anybody who knows the answer can you kindly respond? Thanx!!

  9. agen vivo slot

    When I initially commented I seem to have clicked the -Notify me when new comments are added- checkbox and from now on whenever a comment is added I recieve 4 emails with the exact same comment. Perhaps there is a means you are able to remove me from that service? Appreciate it!

  10. garotas de programa campinas

    Hello! Do you know if they make any plugins to assist with
    Search Engine Optimization? I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good results.

    If you know of any please share. Thank you!

  11. human hair wigs

    human hair wigs
    But here the thing Hillary and Bernie also offered solutions to these people.

    They literally gave speeches to coal mining towns, people
    who had lost factory jobs, etc. And talked about how the government
    would need to help re educate them and help
    find them new jobs in a 21st century workforce..

    full lace wigs Lemon juice is a fruit acid, citric
    acid, and it may temporarily lighten your skin color, just like any skin care product promising to whiten, brighten and
    lighten your complexion. On a weekly basis apply fresh lemon juice
    to your freckled areas and leave it to treat your skin for up to 10 minutes before thoroughly rinsing; mix a pinch of sugar with
    the lemon juice to add exfoliation benefits to your DIY
    treatment [source: Johnson]. Just like a bleaching or fading cream lemon juice is only effective if you use it regularly the effects
    are gradual and you may not start to notice any difference
    until you’ve used it for a few months.. full lace wigs

    360 lace wigs Gabrielle Dee Giffords (born June 8,
    1970) is an American politician from Arizona and a gun control advocate.
    As a member of the United States House of Representatives,
    she represented Arizona’s 8th congressional district from January 3, 2007, until her resignation on January
    25, 2012, after surviving an assassination attempt that left her
    with a severe brain injury. Considered a “Blue Dog” Democrat,[1] her focus on health care reform and illegal immigration were sources of attention for those opposed to her candidacy and made her a
    recipient of criticism from various liberal groups..
    360 lace wigs

    human hair wigs In 1979, Ross released the album The
    Boss. Her 1980 semi eponymous album diana reached number 2 on the US Billboard albums chart,
    and spawned the number one hit “Upside Down”,
    and the Top 5 international hit “I’m Coming Out”.
    Ross’ final single with Motown during her initial run with the company achieved her sixth and final US number one Pop hit,
    the duet “Endless Love”, composed for the Brooke Shields film of the same name, by and performed with Commodores front man, Lionel
    Richie. human hair wigs

    wigs for women As a Texan and H town native, you know
    city pride is a big thing here. I love Clutch Gaming,
    but I really think that more people would love the team if you guys would to
    some sort of documentary, something to get to know
    the team more. An example would be Team Liquid “Squad.”.

    wigs for women

    wigs Wigwags were also manufactured by Union Switch and Signal (US They were primarily
    used in the northeastern US, with a few in Florida,
    although the Frisco had some in the Great Plains. An example was also pictured
    in a review of Hawaiian sugar cane railroads from the 1940s.[4] They were
    manufactured in both a disappearing banner style in the
    East and standard two position in the Great Plains.

    While there are a few examples in museums, the sole surviving US wigwag in service in the US is a two position style in Joplin, MO on an ex Frisco industrial
    spur. wigs

    human hair wigs That she could do that on the same album in which she navigates the pain of her relationship with Brown is emblematic of Rihanna’s
    insistence on being regarded as multifaceted (or, you know,
    human). Rihanna’s narrative as a survivor of abuse is complicated; she refused to be a role
    model, sidestepping the expectation that her road to healing
    would be linear or neat enough for outside parties to understand.
    For a woman in the public eye, that kind of resolve indeed, that kind of rebellion is its own revolution..

    human hair wigs

    human hair wigs Maybe you need to sleep on a satin pillow case or not wear
    hats, something. If your hair dry then it definitely
    going to break more and easier. Picking it out from root to tip also
    can cause a lot of breakage if your hair tangles easy.
    I want them to know I mean business when I ask them
    to do something. But I don want them to be scared of me.

    I don want there to ever be a thought that I might actually physically hurt them, because that
    would never happen. human hair wigs

    Lace Wigs “What might motivate someone to convert an old opal mine into their home and live underground? Oppressive and hostile heat? The prospect of finding opal and becoming rich? A chance to escape your past and start anew? These are questions that come to mind while viewing Tamara Merino’s photo essay on the town of Coober Pedy. A town where 60% of the residents live in underground houses. Surprisingly there’s a warmth to the subterranean spaces and the community, many of which are immigrants, have really redefined the idea of a living/work space.” LG.
    Lace Wigs

    clip in extensions The stage was as huge as a tennis court and was made
    movable. Above the stage was a vast electrical grid, composed
    of truss sections, chain motors, electrical cables and the control devices that linked electronically
    or mechanically with the performances happening below.[17] Four gigantic video monitors formed the backdrop
    of the stage. Other equipment included a mechanical bull and equipment for aerial movements.
    clip in extensions

    costume wigs I have no doubt that Bouchardon will make of this fountain a fine piece of architecture;
    but what kind of fountain has only two faucets where the water porters will come to fill their buckets?

    This isn’t the way fountains are built in Rome to beautify the city.

    We need to lift ourselves out of taste that is gross and shabby.
    Fountains should be built in public places, and viewed from all the gates.
    costume wigs

    wigs for women I would like to see some activity on the site again. In the past I
    asked if anyone would like to contribute, but that never really worked out as the people I have asked already have their own thing going.
    Perhaps I asked the wrong people (in terms of their desire to contibute); I certainly know that they been the right people in terms of their knowledge/capability!.
    wigs for women

    full lace wigs He amassed a record of 12 1 with
    11 wining streak where he won back to back wins over UFC
    veterans Roli Delgado (who was 9 inches taller than Gruetzemacher)[12] and John Gunderson prior signed by UFC.[3][1]Gruetzemacher took 21 months hiatus from his torn ACL injury before he stepped back to cage where
    he was selected as one of the contestants for UFC The Ultimate fighter 22.[13]Gruetzemacher was
    selected to be one of the contestants for the UFC TV MMA competition series The
    Ultimate Fighter: Team McGregor vs. Team Faber, (The Ultimate Fighter 22),
    fighters from Europe coached under Conor McGregor vs fighters from
    United States coached under Urijah Faber.[14] Gruetzemacher was
    coached under team Urijah Faber.[15]He faced Austin Springer on the Elimination fight[16]
    and won the fight via TKO on round two.[17][18] On the Opening Round he was up against Sascha Sharma and won the
    fight via unanimous decision.[19][20] He moved on to the Quarterfinals and faced Artem Lobov and suffered a defeat via TKO.[21][22]In The Ultimate Fighter 22 episode 7 Gruetzemacher had
    a verbal confrontation after a heavy drinking session, betting shots on the billiard games, in the house between Gruetzemacher and Julian Erosa where Erosa commented Gruetzemacher’s fights were the most boring in the house.
    The confrontation escalated and got more heated where Erosa challenged Gruetzemacher
    to act on his aggregation full lace wigs.

  12. How to buy wedding silk sarees

    What’s Going ɗown i’m new to this, I stumbⅼed ᥙpon this I’ve
    discovered It positivеly helpful and it has helped me out loads.

    I hope to contribute & assist dіfferent customers likе іts aiɗed
    me. Great job.

  13. 谷歌优化

    We’re a group of volunteers and opening a brand new scheme in our community.
    Your site provided us with helpful information to work on. You have done an impressive job and our whole
    group will probably be thankful to you.

  14. daftar vivo slot

    Hi, I do believe this is an excellent blog. I stumbledupon it 😉 I will return yet again since I saved as a favorite it. Money and freedom is the best way to change, may you be rich and continue to guide other people.

  15. vivoslot net

    An interesting discussion is definitely worth comment. I do believe that you should publish more on this subject matter, it might not be a taboo matter but generally folks don’t discuss such subjects. To the next! All the best!!

  16. agen vivoslot

    Having read this I thought it was rather enlightening. I appreciate you taking the time and energy to put this article together. I once again find myself spending a lot of time both reading and leaving comments. But so what, it was still worth it!

  17. daftar vivo slot

    Hello, I think your web site could possibly be having browser compatibility issues. When I take a look at your site in Safari, it looks fine however, if opening in IE, it has some overlapping issues. I merely wanted to give you a quick heads up! Besides that, excellent website!

  18. dental veneers Houston

    Great goods from you, man. I’ve understand your stuff previous to
    and you’re just extremely magnificent. I actually like what
    you’ve acquired here, certainly like what you are stating
    and the way in which you say it. You make it entertaining
    and you still take care of to keep it smart. I can’t wait to read far more from
    you. This is really a tremendous site.

  19. vivoslot

    An intriguing discussion is definitely worth comment. I believe that you need to write more on this topic, it may not be a taboo subject but generally people don’t talk about these issues. To the next! Many thanks!!

  20. dingdong togel

    I really love your blog.. Excellent colors & theme. Did you create this amazing site yourself? Please reply back as I’m planning to create my own personal site and would like to find out where you got this from or what the theme is called. Thank you!

  21. dingdong 36d

    Greetings, I think your website could be having internet browser compatibility problems. When I take a look at your blog in Safari, it looks fine however, if opening in IE, it has some overlapping issues. I simply wanted to give you a quick heads up! Besides that, wonderful website!

  22. dingdongtogel.com

    The very next time I read a blog, Hopefully it won’t fail me just as much as this particular one. After all, Yes, it was my choice to read, nonetheless I really thought you’d have something useful to talk about. All I hear is a bunch of crying about something that you could fix if you were not too busy looking for attention.

  23. dingdong togel

    Your style is unique compared to other people I’ve read stuff from. I appreciate you for posting when you’ve got the opportunity, Guess I will just bookmark this site.

  24. qq

    I blog frequently and I genuinely thank you for your content. The article has truly peaked my interest. I am going to book mark your website and keep checking for new information about once a week. I opted in for your RSS feed too.

  25. togel hk

    After checking out a handful of the blog posts on your web page, I honestly like your technique of blogging. I added it to my bookmark site list and will be checking back soon. Take a look at my website too and tell me how you feel.

  26. agen judi online terpercaya

    I was more than happy to find this site. I wanted to thank you for ones time for this particularly wonderful read!! I definitely savored every part of it and i also have you book-marked to check out new stuff in your blog.

  27. judi poker online

    Your style is really unique compared to other people I have read stuff from. Thanks for posting when you’ve got the opportunity, Guess I will just book mark this site.

  28. bandar qq

    I would like to thank you for the efforts you have put in writing this blog. I am hoping to check out the same high-grade content by you later on as well. In truth, your creative writing abilities has encouraged me to get my very own site now 😉

  29. agen judi online

    Oh my goodness! Amazing article dude! Thank you so much, However I am experiencing troubles with your RSS. I don’t understand why I am unable to subscribe to it. Is there anybody else getting identical RSS problems? Anybody who knows the answer can you kindly respond? Thanx!!

Comments are closed.