=IFS(IFS(IFS(IFS

[tarynmahon]

This is probably a stupid question but is there a simpler way of doing loads of IF formulas?
Each IF has different criteria and a different result, I was thinking I could do a SUMPRODUCT but that would only return one result that matches all the different criteria wouldnt it?
The formulas are in Column L onwards, it all works at the moment but I just wanted it to be neater.

Thanks if you can help at all.
Taryn

 

[NARAYANK991]

Hi ,

I doubt that SUMPRODUCT is a suitable function , unless you want to calculate the whole array of results using one formula. At present , every formula is dealing with single cells , and that is no justification for SUMPRODUCT which deals with arrays of data.

What will help is helper columns , especially if there are some intermediate results which can be used in many of the formulae.

Some examples :

Fees!$E$12/2

365*('AHRC - RG72478 Coh1'!$E4-Fees!B$1)

which are used in innumerable places.

Narayan

 

[tarynmahon]

Ok, no worries, thank you for your help
Taryn

 

[tarynmahon]

Is there really no other way, my nested IF has now got completely out of hand!
I tried helper columns but I dont think they will actually help its too complex

 

[Jake Collins]

Dear Tarynmahon

L4 could be simplified to

=IF($G4="Fees",0,Fees!$E$12)*IF($J4="Y",0.4,1)*IF($I4="PT",0.6,1)*IF($E4>Fees!B$2,1,1/365*('AHRC - RG72478 Coh1'!$E4-Fees!B$1))*IF($K4="Y",0.5,1)

A similar technique could be used for the other columns