SOLVED

Help with formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2639821%22%20slang%3D%22en-US%22%3EHelp%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2639821%22%20slang%3D%22en-US%22%3E%3CP%3EHI%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20the%20IF%20formula%20%3DIf(C2%2CC1%2CD1)%20but%20when%20I%20drag%20the%20formula%20down%20to%20other%20rows%2C%20it%20is%20increasing%20the%20numbers%2C%20ie%20%3Dif(C3%2CC2%2CD2)%20can%20anyone%20advise%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2639821%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2639940%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2639940%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1126782%22%20target%3D%22_blank%22%3E%40BellaAliG%3C%2FA%3E%26nbsp%3BWell%2C%20that's%20the%20default%20behavior%20for%20drag%20and%20drop.%20If%20you%20want%20the%20row%20numbers%20to%20stay%20as%20they%20are%2C%20place%20%24%20signs%20in%20front%20of%20the%20row%20references.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20that%20would%20be%3A%26nbsp%3B%3CSPAN%3E%3DIf(C%242%2CC%241%2CD%241)%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2639956%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2639956%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1126782%22%20target%3D%22_blank%22%3E%40BellaAliG%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fswitch-between-relative-absolute-and-mixed-references-dfec08cd-ae65-4f56-839e-5f0d8d0baca9%3Fui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ESwitch%20between%20relative%2C%20absolute%2C%20and%20mixed%20references%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EAs%20an%20example%2C%20if%20you%20copy%20the%20formula%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%3DB4*C4%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Bfrom%20cell%20D4%26nbsp%3Bto%20D5%2C%20the%20formula%20in%20D5%20adjusts%20to%20the%20right%20by%20one%20column%20and%20becomes%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%3DB5*C5%3C%2FSTRONG%3E%3CSPAN%3E.%20If%20you%20want%20to%20maintain%20the%20original%20cell%20reference%20in%20this%20example%20when%20you%20copy%20it%2C%20you%20make%20the%20cell%20reference%20absolute%20by%20preceding%20the%20columns%20(B%20and%20C)%20and%20row%20(2)%20with%20a%20dollar%20sign%20(%3C%2FSPAN%3E%3CSTRONG%3E%24%3C%2FSTRONG%3E%3CSPAN%3E).%20Then%2C%20when%20you%20copy%20the%20formula%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%3D%24B%244*%24C%244%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Bfrom%20D4%20to%20D5%2C%20the%20formula%20stays%20exactly%20the%20same.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIn%20your%20case%20it%20would%20be%3A%26nbsp%3B%3DIf(C%242%2CC%241%2CD%241)%20or%26nbsp%3B%3DIf(%24C%242%2C%24C%241%2C%24D%241)%26nbsp%3B%20%2C%26nbsp%3Bdepending%20on%20your%20reference.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20Community%20members%20reading%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

HI 

I am using the IF formula =If(C2,C1,D1) but when I drag the formula down to other rows, it is increasing the numbers, ie =if(C3,C2,D2) can anyone advise

4 Replies

@BellaAliG Well, that's the default behavior for drag and drop. If you want the row numbers to stay as they are, place $ signs in front of the row references. 

So that would be: =If(C$2,C$1,D$1) 

 

best response confirmed by BellaAliG (New Contributor)
Solution

@BellaAliG 

 

Switch between relative, absolute, and mixed references

As an example, if you copy the formula =B4*C4 from cell D4 to D5, the formula in D5 adjusts to the right by one column and becomes =B5*C5. If you want to maintain the original cell reference in this example when you copy it, you make the cell reference absolute by preceding the columns (B and C) and row (2) with a dollar sign ($). Then, when you copy the formula =$B$4*$C$4 from D4 to D5, the formula stays exactly the same.

 

In your case it would be: =If(C$2,C$1,D$1) or =If($C$2,$C$1,$D$1)  , depending on your reference.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

 

 

@Riny_van_Eekelen Thank you

Thank you this was very informative and helpful.