pulling diferent conditions with the IFS fuction

Question

Hello I am trying utilize the IFS function to calculate additional footages pas a certain Unit criteria. based on the length of a short, medium, or long unit and whether it is a normal unit, branch unit or transfer unit as different factors. the maximum length for a long normal unit is 120 ft, for any length branch unit is 25 ft, and for any length transfer is 5 ft. I came up with the formula below but am not getting the right output.

=IFS(G4="long"&F4>120,F4-120,I4="branch"&F4>25,F4-25,I4="transfer"&F4>5,F4-5)

PHoto of table for referenced:

oliverscheurich · Answer

rstrebe&nbsp;=IFS(AND(G4="long",F4&gt;120),F4-120,AND(I4="branch",F4&gt;25),F4-25,AND(I4="transfer",F4&gt;5),F4-5)or=IFS((G4="long")*(F4&gt;120),F4-120,(I4="branch")*(F4&gt;25),F4-25,(I4="transfer")*(F4&gt;5),F4-5)&nbsp;It works in my sheet when i use the formula like this.

sergeibaklan · Answer

rstrebe&nbsp;
Perhaps
=IFS(
  I4 = "Barnch",   MAX(0, F4-5),
  I4 = "Transfer", MAX(0, F4-25),
  G4 = "Long",     MAX(0, F4-120),
  TRUE, 0
)
if I understood the logic correctly.

Forum Discussion

pulling diferent conditions with the IFS fuction

Share

Resources