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

"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

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

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

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

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