Flag 1 in 5 entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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","")
Click to expand...

I think you meant to use 5, not 4, in you formulas... the OP asked for "1
case in every 5 for audit".

Rick
 
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
 
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
 
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
 
Back
Top