Forum Discussion

rach1345's avatar
rach1345
Brass Contributor
Jan 25, 2023
Solved

Formula returning the wrong value

Hi,

I have created a file where I have have asked excel to calculate fields based on the following but it not calculating the Pallet fields correctly

if st = 3001 = "n/a", c2 = "in" ="gexpbook", e2=gdg ="gdg", d2=1 "gline", and then here i am asking if to do a volume calculation so if it is more 0.1 in volume (cell a2) but less than or equal to 53000 (fifty three thousand) to return "g1carton", if volume is more than 53000 but less than 1 million to return g1>carton, if it is 1000000 to return G1PALLET but if it is more than 1 pallet (1million) to return G>1PALLET.  What do I need to change to make the formulas work out the volumes for the pallets correctly?  Many thanks Rach

Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Jan 25, 2023

    rach1345 

    For such nested IF queries you can use the IFS function.

    According to your specifications, the formula should look like this:

    =IFS(B2=3001,"N/A",
C2="IN","GEXPBOOK",
E2="GDG","GDG",
D2=1,"GLINE",
AND(A2>0.1,A2<=53000),"G1CARTON",
AND(A2>53000,A2<1000000),"G1>CARTON",
A2=1000000,"G1PALLET",
A2>1000000,"G>1PALLET",
TRUE,"0")

     

2 Replies

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Jan 25, 2023

    rach1345 

    For such nested IF queries you can use the IFS function.

    According to your specifications, the formula should look like this:

    =IFS(B2=3001,"N/A",
C2="IN","GEXPBOOK",
E2="GDG","GDG",
D2=1,"GLINE",
AND(A2>0.1,A2<=53000),"G1CARTON",
AND(A2>53000,A2<1000000),"G1>CARTON",
A2=1000000,"G1PALLET",
A2>1000000,"G>1PALLET",
TRUE,"0")

     

Resources