Excel Magic Trick 1373: Formula to Extract Every Nth Value & Conditionally Format Every Nth Value
HTML-код
- Опубликовано: 5 авг 2024
- Download File: people.highline.edu/mgirvin/ex...
See how to Conditionally Format Every 5th Value and Extract Every 5th Value:
1. Introduction
2. Formula 1 for counting how many values will be extracted. ROWS & ROUNDDOWN Functions.
3. Formula 1 for counting how many values will be extracted. MAX and INT Functions.
4. Conditional Formatting Every 5th (Every Nth Row in Data Set) using a Logical Formula and the function: MOD Function. Discussion about why we use ROWS for Number Incrementor Formulas and not ROW function.
5. Extract Every 5th Row (Every Nth Row in Data Set) using INDEX, ROWS, and IF Function. Discussion about why we use IF Function and an Alternative Logcial Test rather than the IFERROR Function.
6. Summary.
I have been watching some of your videos over the past week or two and my knowledge and understanding of what the most common functions in Excel (/Google Sheets) are and how they can be used in conjunction with each other has been nothing short of a revelation. Thank you so much for putting this content out there. I started a new job role about a month ago and the core payload in our division of the business is one of our Excel spreadsheets in particular. My knowledge of Excel before starting this role was bare minimum at best and thanks to various different outlets on the world wide web, (your RUclips channel included), I'm making our workflow incrementally more efficient over time, the more I'm learning. How fantastic is that? Many, many thanks.
Your energy has made my day, planning to follow all your tutorials soon. Thank you very much and keep it up
Las Vegas, NV, USA: This is so very awesome! Thank you so much!!
You are welcome so much!
Thanks sir. I have learnt Excel from your videos. You are amazing.👏👏
just these key board short cuts where worth the watch
Keyboards rule : )
You're a lifesaver. Thank you 🙇
Thank you, I was searching for ages for this.
thank you so much! I spent the last 30 minutes trying to do something that Icould have done in 3 minutes
Thanks for a video and all your work in this chanel!
You are welcome!
Thank u Mike for this great Video 📹 👍👍
thanks a lot!! You helped me with my data
Thank you for sharing this! I have a ton of data, is there a way to autofill the "true-false" formulae to the end of my dataset rather than dragging it down the squares? I seem to ahve tried the typical ones and it hasn't worked for me. Perhaps I have to do it at a different time point for it to work rather than at the end? Thank you
Perfect - thanks!
I think that we can to realice with function indirect for do not use this two column of thrue and false ?
we can to use the function indirect and row for to do it of form direct or not ??
is there a way of doing this for multiple columns?
Great Trick Mike!!!
Glad you though it was great : )
Always enjoy and learn something from your videos! Would love to see how you would use Power Query to extract an entire row of data based off the max value of one of the columns. Kind of like a sub-query in SQL. (Example: max invoice number from a work order, etc. Thanks!
Maybe:
Excel Magic Trick 1334: Power Query or Formula: Lookup Name Based on Max Value and more…
ruclips.net/video/SexKsU4qDqM/видео.html
How about using this in tandem with extracting all null values in the same sales column that we're extracting the max values from? So it shows the max sales and ALL the null values as well?
Thanks a lot man A genius tutorial
Awesome as always :)
Glad it is awesome! : )
Wow, great stuff.
Glad you like it!
You're a saint.
I love your creative fun with excel !!!
Maybe you should announce a competition: "How many ways of doing this can you invent?" ;-)
Of course, for fun only.
Back to problem.... we can also do this by Advanced Filter with formula: for example =NOT(MOD(ROWS(A$4:A4),$D$4))
Thanks for your all awesome videos!!
Advanced Filter is a great way to do it!! I have used that method in extracting records to audit from large data sets. Very Efficient : )
I love the idea of seeing how many ways to do it. Mr Excel did a contest like that back in 2007 for detecting whether one of two text strings were present in a large text string and then were about 100 entries. Barry Houdni at Mr Excel Message Board won it with a LOOKUP/SEARCH formula. I will keep your idea of a contest in the back of my mind for a future fun event... : )
I must write more efficient formulas
I must write more efficient formulas
I must write more efficient formulas
I must write more efficient formulas
I must write more efficient formulas
I must write more efficient formulas
I always use the formula to test for errors and some of my sheets take many seconds to calculate. I need to get into the habit of thinking things out more.
Thanks for these tips. I will become a better person because of them :)
Anyone know how to stop the reference from skipping numbers when pasting into every other cell? I am using =IF(NAMELIST!$D1="","",NAMELIST!$D1) but in row three it uses D3 then 5
then 7. I just want to skip rows but count normally D1 D2 D3. Any
ideas?
thanks a lot Mike !!!
best regards !!
You are welcome!
great man
No doubt you have made the Excel a "FUN" ;D
Fun for sure : )
Thank you so much! :)
You are welcome!
If you wanted to run a forecast linear formula in batches of 5 or 6 rows how would you do that?
very helpful
what formulae we gonna use for creating every nth value which is Red in Headings?
Hi .
Can u please share how we can add if first is is after 5 rows then next is after 4 rows next value after 7 rows . I mean in case of uneven !
Please share
correct, thanks Mike
Awesome!
how can you apply this to do it by columns instead of rows?
Efficiency is good! :)
Yes it is : )
Does this work is its flipped so theres only two rows
fantastic as usual!
you mentioned not using index function for multiple table lookups as there are better ways.. do you have any videos on this?
Cheers
I have lots of videos on multiple table lookup. here is about the most comprehensive:
Excel Magic Trick 1316: VLOOKUP with Multiple Lookup Tables: IF, INDIRECT, SWITCH, IFS, or CHOOSE?
ruclips.net/video/R5RVdzsoVIo/видео.html
Thanks Mike.
You are welcome, WRH!!!
Sir, can you please upload a video on "How to extract records with every alternative cell". I mean to say, skipping every alternative cell blank when extracting the records from raw data.
The Logic of Rows x 5 was a smart move !!!!
I think I learned it from Aladin at the Mr Excel Message Board back in 2005.
VERY NICE !!
I LIKE TOO MUCH "
EXCELENT !
Glad you like it "TOO" much!! : )
Great stuff! Wondering how to do this in Power Query
Hi great video! I have a question:
Is there any way to start counting in a in cell B4 and then increments by 5? That is, B4 being chosen because another cell was 4. So if that "other" cell was 6 for example then I wanna start counting at B6 and then increments of 5.
I can't figure it out, I would really appreciate your help! Thank you amazing videos!
This can be done in two ways: -
1. You can use delete entire row function four times to get the desired data.
2. You can use combination of delete entire row and list nth row function to get the desired data. The list nth row function should be used once the desired data becomes multiple of 2 or 3 etc
Thanks loads
You are welcome loads, Ben!!
Hello i hope so you will be fine as i am i have scenario and hope for you give solution of it "I want to maintain my stock through invoice automatically as i enter quantity of specific item in invoice it will reduced stock of that specific Quantity from inventory stock sheet as i maintaine separately on excel and shall not move back to previous stock quantity as i make new invoice.In present i am able to reduced stock through invoice by search if formula but stock dnt remain still and move back to last stock quantity as i make new invoice " kindly help me out thanks
I have a list of values I can copy, they are line by line. I need them pasted into every 3rd cell so 1 to cell 3 2 to cell 6 3 to cell 9.
Very smart "ROWS(H$2:H2)*$D$2" to reach the position :-)
Glad you like it!
sir how do you change number from headers also . based on $D $4
You can manually remove them by highlighting and then Deleting. Or if the cell in your formula is in Edit Mode and your Cursor is touching it, on a PC Excel computer, you can use the F4 key. If your keyboard requires the Fn key, use Fn F4.
but how do you extract every - say - 30th cell???
Awesome
Glad you like it : )
What software do you use for capturing the video?
Camtasia
Tu! those keystrokes are very helpful!
Glad they help!
Hello sir can I download your all RUclips videos file on a click.
I need your help sir
they realy should create =null() and =blank() functions.
the download link does not work
Server down, back up soon.
Sadly, not working in 2021 anymore.
1373