Flag 1 in 5 entries
Hi
I need to write a formula which will enable my spreadsheet to flag up 1 case in every 5 for audit (was thinking of counting 1 in 4 cases and having a flag saying next case to be audited) As my spreadsheet runs over the year i have implemented a total count of cases for the year. But what i need to know is the formula to flag every 4th case and also is there a way of putting the message (next case to be audited) into a pop up box? Many thanks for your help Matt |
Flag 1 in 5 entries
Hi
Use a helper column. If your list start on row 1 use =IF(MOD(ROW(),4)=0,"Next case to audit","") If your list starts on row 2 use =IF((MOD(ROW()-1,4))=0,"Next case to audit","") Regards Peter "tuk16664" wrote: Hi I need to write a formula which will enable my spreadsheet to flag up 1 case in every 5 for audit (was thinking of counting 1 in 4 cases and having a flag saying next case to be audited) As my spreadsheet runs over the year i have implemented a total count of cases for the year. But what i need to know is the formula to flag every 4th case and also is there a way of putting the message (next case to be audited) into a pop up box? Many thanks for your help Matt |
Flag 1 in 5 entries
Use a helper column. If your list start on row 1 use
=IF(MOD(ROW(),4)=0,"Next case to audit","") If your list starts on row 2 use =IF((MOD(ROW()-1,4))=0,"Next case to audit","") I think you meant to use 5, not 4, in you formulas... the OP asked for "1 case in every 5 for audit". Rick |
Flag 1 in 5 entries
Thank you very much for your reply, now i maybe thick asking this question
but here goes. Say i already have the total number of case i want to work out the 1 in 4 entries from in cell B6 how would this formula work it out from there? Many thanks "Rick Rothstein (MVP - VB)" wrote: Use a helper column. If your list start on row 1 use =IF(MOD(ROW(),4)=0,"Next case to audit","") If your list starts on row 2 use =IF((MOD(ROW()-1,4))=0,"Next case to audit","") I think you meant to use 5, not 4, in you formulas... the OP asked for "1 case in every 5 for audit". Rick |
Flag 1 in 5 entries
Do you want 1 in 5 like in your subject line or 1 in 4?
=IF((MOD(ROWS($A$1:A1)-1,5))=0,"Next case to audit","") or =IF((MOD(ROWS($A$1:A1)-1,4))=0,"Next case to audit","") you can put them in any cell -- Regards, Peo Sjoblom "tuk16664" wrote in message ... Thank you very much for your reply, now i maybe thick asking this question but here goes. Say i already have the total number of case i want to work out the 1 in 4 entries from in cell B6 how would this formula work it out from there? Many thanks "Rick Rothstein (MVP - VB)" wrote: Use a helper column. If your list start on row 1 use =IF(MOD(ROW(),4)=0,"Next case to audit","") If your list starts on row 2 use =IF((MOD(ROW()-1,4))=0,"Next case to audit","") I think you meant to use 5, not 4, in you formulas... the OP asked for "1 case in every 5 for audit". Rick |
Flag 1 in 5 entries
You have me a little confused on how often you want the flagged row to
appear (Billy, you may have been right<g). You stated in your original post that you wanted to flag 1 in 5 entries, but later on you sort of said 1 in 4. Now, in this message, you are again saying 1 in 4. Anyway, depending on if it is 1 in 4 or 1 in 5, you will need to pick the right number as indicated by Billy and me in our previous posts. Anyway, to use what Billy suggested, select a blank column, place his formula in the row of that column which corresponds to the first row of your data, and copy it down. Every 4th or 5th (depending on what number you finally settled on) will be flagged with the message "Next case to audit" Rick "tuk16664" wrote in message ... Thank you very much for your reply, now i maybe thick asking this question but here goes. Say i already have the total number of case i want to work out the 1 in 4 entries from in cell B6 how would this formula work it out from there? Many thanks "Rick Rothstein (MVP - VB)" wrote: Use a helper column. If your list start on row 1 use =IF(MOD(ROW(),4)=0,"Next case to audit","") If your list starts on row 2 use =IF((MOD(ROW()-1,4))=0,"Next case to audit","") I think you meant to use 5, not 4, in you formulas... the OP asked for "1 case in every 5 for audit". Rick |
All times are GMT +1. The time now is 02:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com