SOLVED

IF Formula assistance

%3CLINGO-SUB%20id%3D%22lingo-sub-2346864%22%20slang%3D%22en-US%22%3EIF%20Formula%20assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2346864%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20IF%20statement%20under%20my%20'RAW'%20tab%20and%20I%20think%20that%20should%20be%20a%20quick%20fix%20for%20this%20group%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20IF%20statement%20under%20my%20'Resources'%20column%20does%20exactly%20I%20want%20it%20to%2C%20but%20I%20have%20my%20'Resource%20Hours'%20column%20set%20to%20(%5BResources%5D*60)%20to%20show%20in%20minutes.%26nbsp%3B%20This%20works%20great%20if%20I%20have%20Salesforce%20cases%20represented%2C%20but%20if%20it%20is%20'Self-Reported%20Time'%20-%20then%20not%20sure%20much%2C%20because%20I%20just%20want%20the%20raw%20processing%20time.%26nbsp%3B%20How%20can%20I%20fix%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22example%201.jpg%22%20style%3D%22width%3A%20408px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F280109i9AE842AEF68CE829%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22example%201.jpg%22%20alt%3D%22example%201.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI'm%20also%20attaching%20a%20copy%20of%20my%20file%20to%20see%20if%20anyone%20can%20help%20me%20out%3F%3C%2FP%3E%3CP%3EBasically%2C%20everything%20works%20as%20it%20should%20EXCEPT%20if%20I%20have%20a%20'Self-Reported%20entry'%20-%20I%20need%20a%20formula%20that%20will%20just%20read%20the%20processing%20time%20(WITHOUT)%20multiplying%20it%20by%2060.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20thoughts%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2346864%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2346920%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2346920%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F606460%22%20target%3D%22_blank%22%3E%40NeilKloster%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%20this%20formula%20for%20Resource%20Hours%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D%5B%40Resources%5D*IF(%5B%40%5BTracking%20Type%5D%5D%3D%22Self-Reported%20Time%22%2C1%2C60)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2346938%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2346938%22%20slang%3D%22en-US%22%3EBoom!!%20Thank%20you!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2346949%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2346949%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EApologies%20-%20but%20I%20have%20another%20issue%20that%20I%20just%20noticed.%20For%20my%20'Resource'%20Column%2C%20I%20then%20need%20it%20divide%20that%20raw%20processing%20time%20for%20those%20'Self-Reported%20Cases'%20by%2060.%20What%20change%20would%20I%20need%20to%20make%3F%3C%2FLINGO-BODY%3E
Contributor

Hello, 

 

I have an IF statement under my 'RAW' tab and I think that should be a quick fix for this group: 

 

The IF statement under my 'Resources' column does exactly I want it to, but I have my 'Resource Hours' column set to ([Resources]*60) to show in minutes.  This works great if I have Salesforce cases represented, but if it is 'Self-Reported Time' - then not sure much, because I just want the raw processing time.  How can I fix this?

 

example 1.jpg

I'm also attaching a copy of my file to see if anyone can help me out?

Basically, everything works as it should EXCEPT if I have a 'Self-Reported entry' - I need a formula that will just read the processing time (WITHOUT) multiplying it by 60.  

 

Any thoughts?

8 Replies
best response confirmed by NeilKloster (Contributor)
Solution

@NeilKloster 

Use this formula for Resource Hours:

 

=[@Resources]*IF([@[Tracking Type]]="Self-Reported Time",1,60)

Boom!! Thank you!!
@Hans Vogelaar

Apologies - but I have another issue that I just noticed. For my 'Resource' Column, I then need it divide that raw processing time for those 'Self-Reported Cases' by 60. What change would I need to make?

@NeilKloster 

I think

 

=IF([@[Tracking Type]]="Self-Reported Time",[@[Processing Time]]/60, INDEX(Shared_Services_Expectations[Expectations (Hours)], MATCH([@[Service Level 1]]&[@[Service Level 2]], Shared_Services_Expectations[Category Level 1]&Shared_Services_Expectations[Category Level 2], 0)))*[@[Service Count]]

 

but there seems to be a problem with referring to the Shared_Services_Expectations table - the formula somehow doesn't recognize it. Perhaps it's just my PC.

@Hans Vogelaar 

 

It's not just your PC.  It does this to the Resource Hours Column: 

 

example 2.jpg

 

Which granted is not a HUGE deal - it all still works, but would rather figure out a way to just have the processing time show there if it is a Self-Reported entry.  Not sure if there is a way to figure that piece out or not?  

 

Appreciate your help! 

@Hans Vogelaar 

 

Attached is another file - similar concept, different processes - but here is the formula that they used under the RAW tab: 

 

=IF([@[Service Type]]="Self-Reported Time",[@[Processing Time]],VLOOKUP([@Service],Expectations[#All],2,FALSE)*[@[Service Count]]) 

 

Not sure how to get that to translate over to mine or not?  Also not sure why my expectations table will not connect as well?

@NeilKloster 

I think we were making it too complicated. See the attached version.

@Hans Vogelaar - you are amazing! And yes, looks like we were! Thank you SO much for the assistance.