IF Function

%3CLINGO-SUB%20id%3D%22lingo-sub-129379%22%20slang%3D%22en-US%22%3EIF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-129379%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20having%20problems%20figuring%20out%20an%20%22IF%22%20formula%2C%20I'm%20wondering%20if%20anyone%20can%20help%20me%20out.%20%26nbsp%3BThe%20question%20is%20as%20follows%3A%20%26nbsp%3BIf%20the%20Repeat%3F%20value%20is%20%22YES%22%2C%20calculate%20the%20amount%20paid%20by%20subtracting%205%20from%20the%20Fee.%20%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20missing%20something%20in%20the%20formula%20that%20if%20the%20repeat%20value%20is%20%22NO%22%20it%20stays%20the%20same%2C%20not%20sure%20how%20to%20add%20that.%20%26nbsp%3BCan%20anyone%20help%20me%20out%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EDiane%3C%2FP%3E%3CDIV%20class%3D%22grammarly-disable-indicator%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-129379%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2294759%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2294759%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20it's%20been%20years%2C%20but%20your%20formula%20still%20holds%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EWhat%20if%20I%20want%20multiple%20cells%20subtracted%20from%20one%20particular%20cell%2C%20so%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DA1-(B1%3D%22yes%22)*5%3C%2FP%3E%3CP%3E%3DA1-(B2%3D%22yes%22)*3%3C%2FP%3E%3CP%3E%3DA1-(B3%3D%22yes%22)*8%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20make%20this%20one%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-129681%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-129681%22%20slang%3D%22en-US%22%3E%3CP%3ESergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20a%20very%20smart%20algorithm!%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-129555%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-129555%22%20slang%3D%22en-US%22%3E%3CP%3EOr%20the%20same%3C%2FP%3E%3CPRE%3E%3DA2-(B2%3D%22Yes%22)*5%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-129384%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-129384%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Diane%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22IF.JPG%22%20style%3D%22width%3A%20394px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F24443i2B79F9F3CFE05316%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22IF.JPG%22%20alt%3D%22IF.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2295139%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2295139%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037315%22%20target%3D%22_blank%22%3E%40Rach31%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20mean%3C%2FP%3E%0A%3CP%3E%3DA1-(B1%3D%22yes%22)*5%20-%26nbsp%3B(B2%3D%22yes%22)*3%20-%20(B3%3D%22yes%22)*8%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I'm having problems figuring out an "IF" formula, I'm wondering if anyone can help me out.  The question is as follows:  If the Repeat? value is "YES", calculate the amount paid by subtracting 5 from the Fee.  

I'm missing something in the formula that if the repeat value is "NO" it stays the same, not sure how to add that.  Can anyone help me out? 

 

Thanks,

Diane

 
7 Replies

Hello Diane,

 

Try this:

IF.JPG

 

Or the same

=A2-(B2="Yes")*5

Sergei,

 

It's a very smart algorithm!

Thank you!

@Sergei Baklan 

 

I know it's been years, but your formula still holds

What if I want multiple cells subtracted from one particular cell, so:

 

=A1-(B1="yes")*5

=A1-(B2="yes")*3

=A1-(B3="yes")*8

 

Is there a way to make this one formula?

 

Thanks!

@Rach31 

Perhaps you mean

=A1-(B1="yes")*5 - (B2="yes")*3 - (B3="yes")*8

Wow, thanks! F1) for your quick reply and 2) for another helpful formula.

Can I ask you one more thing?

In A1 I have a sum for numbers in B1 to B10 (which is my monthly bills)
In C1 to C10 I have made a list with paid "Yes" or "No".

Some of the bills have to be paid every 3 months (instead of per month).
If for example B4 is to be paid quarterly and I list C4 (Paid?) as "No", is there a way for B4 not to be added in the sum in A1?

 

What I have now (sorry, it's in Dutch) is:

=SOM(N10:N31)

 

I would like it to be:

=SOM(N10:N31) - N15 if M15 is "No" and - N18 if M18 is "No"

@Rach31 

That's better to discuss with the sample file. In general, to sum from B1:B10 only values against which in C1:C10 is "No" could be

=SUMIF(C1:C10,"no",B1:B10)