SOLVED

Addition to an existing formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2263119%22%20slang%3D%22en-US%22%3EAddition%20to%20an%20existing%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2263119%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20following%20formula%20that%20works%20well%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D(IF(AND(H13%3D1%2CE33%3D%22%22)%2CINDEX(Sheet1!C4%3AH30%2CMATCH((E31)%2CSheet1!A4%3AA30)%2CMATCH(H15%2CSheet1!C3%3AH3%2C0))%2CIF(H13%3D2%2CINDEX(Sheet1!L4%3AQ30%2CMATCH((E31%2BE33)%2CSheet1!J4%3AJ30)%2CMATCH(H15%2CSheet1!L3%3AQ3%2C0))%2C%22Check%20Entry%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20add%20a%20extra%20expense%20to%20this%20formula%20where%20if%20H14%20has%200%20there%20is%20no%20additional%20expense%2C%20if%20H14%20has%201%20in%20it%20then%20%24328%20needs%20to%20be%20added%20to%20the%20sum%20of%20the%20above%20formula%2C%20if%20H14%20has%202%20or%20more%20in%20it%20then%20%24492%20needs%20to%20be%20added%20to%20the%20sum%20of%20the%20above%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20assist%20and%20help%20me%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20Scott%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2263119%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hi,

 

I have the following formula that works well:

 

=(IF(AND(H13=1,E33=""),INDEX(Sheet1!C4:H30,MATCH((E31),Sheet1!A4:A30),MATCH(H15,Sheet1!C3:H3,0)),IF(H13=2,INDEX(Sheet1!L4:Q30,MATCH((E31+E33),Sheet1!J4:J30),MATCH(H15,Sheet1!L3:Q3,0)),"Check Entry")))

 

I need to add a extra expense to this formula where if H14 has 0 there is no additional expense, if H14 has 1 in it then $328 needs to be added to the sum of the above formula, if H14 has 2 or more in it then $492 needs to be added to the sum of the above formula.

 

Can anyone assist and help me?

 

Thanks in advance Scott

 

4 Replies

@Scott Miller I take your word for it that the formula works, though I believe you have quite a few unnecessary brackets in it. Anyhow, add this to the formula:

+(H14=1)*328+(H14=2)*492

and it should add the additional expenses based on what's in H14 (1 or 2).

@Riny_van_Eekelen 

 

Thanks for the add, the only thing it seems to get caught up on is if you place more than 2 in H14 then it reverts back to the answer of have nothing in H14?

 

Is there a way of showing the answer with 1 in H14 (which it does now) and the answer with 2 (which it does now), but leave the answer of having 2 if there is a value higher the 2?

 

So if you place a 6 (or any number above 2) in H14 it would only show the answer of having 2 in H14?

 

Cheers Scott

best response confirmed by Scott Miller (New Contributor)
Solution

@Scott Miller Sorry, I  missed the "2 or more" part  your original question. Change the latter part of the extras to :

(H14>=2)*492

 Any number greater than or equal to 2 will add 492.

Perfect - Thank you.