If problem

[gmazza76]

Afternoon,

I have a multiple IF problem.

I am suing the following Sum to identify if an agent is on shift or not so i can work out productivity of an agent

=IF(C4="ON SHIFT",C26/C33,C4)

My issue is that it is upto an agent to put a figure in "C33" i get a #DIV/0! error. Is there anything i can do so that #DIV/0! doesnt appear and replace it with a 0 or a message in the cell "Missing Calls"

If possible a formula answer would be best as there a 6 places on the sheet that this can occur or a VB answer that can cover this in 1 go.

Many Thanks

 

[VoG]

Try

=IF(C33="","Missing data",IF(C4="ON SHIFT",C26/C33,C4))

 

[justme1122]

=if(iserror(C4="ON SHIFT",C26/C33,C4),0,IF(C4="ON SHIFT",C26/C33,C4))

 

[MrKowz]

Another alternative to handle if they might enter 0 into C33:

=IF(VALUE(C33)=0,"Missing Data",IF(C4="ON SHIFT",C26/C33,C4))

 

[justme1122]

=if(iserror(C4="ON SHIFT",C26/C33,C4),0,IF(C4="ON SHIFT",C26/C33,C4))

Sorry, typo in the iserror

=IF(ISERROR(IF(C4="ON SHIFT",C26/C33,C4)),0,IF(C4="ON SHIFT",C26/C33,C4))

 

[gmazza76]

Many Thanks to you all.

 

[gmazza76]

Good Afternoon,

Thanks for the formula, but i have a slight problem.

=IF(VALUE(C33)=0,"Missing Data",IF(C4="ON SHIFT",C26/C33,C4))

Is there any chance the formula can check "C4" to see if it has "ON SHIFT" before it checks C33. The reason i need it to do this is so that if it doesnt say on shift it will leave the reference other than "ON SHIFT" instead of missing data. I need it to leave the other options instead which are HOLIDAY, ILL or RDO.

Cheers

 

[VoG]

Try

=IF(C4<>"ON SHIFT",C4,IF(VALUE(C33)=0,"Missing data",C26/C33))

 

[justme1122]

Try just throughing another if statment around it like this:

=if(c4="ON SHIFT",IF(VALUE(C33)=0,"Missing Data",IF(C4="ON SHIFT",C26/C33,C4)),c4)