How to use logical condiiton

%3CLINGO-SUB%20id%3D%22lingo-sub-1493675%22%20slang%3D%22en-US%22%3EHow%20to%20use%20logical%20condiiton%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1493675%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20to%20use%20logical%20formula.%20one%20column%20contains%20%25%20of%20withdrawal%20amt%20and%20one%20column%20contains%20Expenses%20.if%20i%20want%20to%20change%20expenses%20column%20the%20percentage%20column%20should%20accordingly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1493675%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-1493685%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20logical%20condiiton%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1493685%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20question%20is%20not%20clear.%20Kindly%20reword%20the%20question%20and%2For%20upload%20a%20sample%20file%20%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1493689%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20logical%20condiiton%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1493689%22%20slang%3D%22en-US%22%3EOr%20you%20mean%20something%20like%20this%20%3CBR%20%2F%3E%3CBR%20%2F%3EColumn%20A.%20Column%20B%3CBR%20%2F%3ECell%20A2%3A%205%25%20Cell%20B2%3A%2050%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20cell%20C2%20%3DA2*B5%20which%20is%20equal%20to%202.5%3CBR%20%2F%3E%3CBR%20%2F%3E%3F%3F%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1493844%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20logical%20condiiton%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1493844%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20the%20file%2C%20Figures%20in%20coloumn%20G%20should%20not%20go%20negative%20if%20i%20change%20the%20figures%20in%20coloumn%20F.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1494293%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20logical%20condiiton%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1494293%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711925%22%20target%3D%22_blank%22%3E%40BABA1978%3C%2FA%3E%26nbsp%3BThe%20expense%20in%20column%20F%20seem%20to%20be%20predetermined%20(fixed%20amount%20plus%20periodic%20inflation%20adjustments).%20If%20you%20then%20want%20to%20avoid%20the%20amounts%20in%20column%20G%20to%20go%20negative%2C%20the%20SWP%20Monthly%20amount%20in%20columns%20C%20may%20not%20be%20lower%20than%20the%20amount%20in%20F.%20In%20generic%20term%2C%20the%20formula%20in%20C%20would%20be%20like%3A%20%22%3DMAX(F%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3En%3C%2FFONT%3E%3C%2FSTRONG%3E%2C%3CSWP%20monthly%3D%22%22%20formula%3D%22%22%3E)%22%2C%20where%20%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3En%3C%2FFONT%3E%3C%2FSTRONG%3E%20is%20the%20row%20number.%3C%2FSWP%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20be%20more%20specific%2C%20on%20row%2024%20(%3D%20Month%2021)%2C%20the%20formula%20in%20C24%20would%20be%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMAX(F24%2C(B24*3.5%2F100)%2F12)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BThis%20will%20return%2090825%20in%20C24%2C%20resulting%20in%200%20in%20G24%20rather%20than%20-181.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1494412%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20logical%20condiiton%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1494412%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20reply.%20requires%20more%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20percentage%20in%20C24%20remains%20static.%20If%20we%20increase%20or%20decrease%20expense%20the%20percentage%20(in%20C24)should%20change%20accordingly%20and%20balance%20column%20should%20not%20go%20negative%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1494452%22%20slang%3D%22en-US%22%3ERE%3A%20How%20to%20use%20logical%20condiiton%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1494452%22%20slang%3D%22en-US%22%3EI%20am%20using%20the%20autosum%20and%20when%20I%20add%20a%20bunch%20of%20numbers%20the%20result%20is%20always%20zero%20why%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1494455%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20logical%20condiiton%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1494455%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711925%22%20target%3D%22_blank%22%3E%40BABA1978%3C%2FA%3E%26nbsp%3BPerhaps%20you%20can%20start%20by%20explaining%20the%20logic%20for%20the%20changes%20in%20C.%20Currently%2C%20the%20uplifts%20are%20hardcoded%20in%20every%20cell%20and%20are%20far%20from%20static%20(starting%20with%203.5%2C%20then%209.9%20in%20month%2012%2C%20thereafter%203.6%20but%20then%20in%20month%2021%20it%20goes%20down%20to%203.5%20again.%20Then%209.5%20in%20month%2024%20and%20then%205.9%20from%20month%2025%20onwards%20etc.%20etc.%20Could%20it%20be%20that%20you%20simply%20forgot%20to%20change%20the%20factors%20for%20months%2021%20through%2023%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20expense%20column%20you%20have%20amounts%20with%20a%20note%20every%2012th%20month%20that%20mentions%20an%20uplift%20for%20inflation%20by%205%25%20(plus%2060K%20for%20each%2012th%20month).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20automate%20anything%2C%20it's%20important%20that%20you%20clearly%20define%20the%20rules%20for%20it%20first.%20These%20can%20probably%20be%20structured%20in%20a%20table%20that%20allows%20you%20to%20pick-up%20the%20correct%20percentages%2C%20inflation%20adjustments%20and%20yearly%20extra%20expenses.%20Just%20guessing%20though.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

How to use logical formula. one column contains % of withdrawal amt and one column contains Expenses .if i want to change expenses column the percentage column should accordingly.

7 Replies
Hello,

The question is not clear. Kindly reword the question and/or upload a sample file

Cheers
Or you mean something like this

Column A. Column B
Cell A2: 5% Cell B2: 50

In cell C2 =A2*B5 which is equal to 2.5

???

@Abiola1 

 

I have attached the file, Figures in coloumn G should not go negative if i change the figures in coloumn F. 

@BABA1978 The expense in column F seem to be predetermined (fixed amount plus periodic inflation adjustments). If you then want to avoid the amounts in column G to go negative, the SWP Monthly amount in columns C may not be lower than the amount in F. In generic term, the formula in C would be like: "=MAX(Fn,<SWP Monthly formula>)", where n is the row number.

 

To be more specific, on row 24 (= Month 21), the formula in C24 would be:

=MAX(F24,(B24*3.5/100)/12)

 This will return 90825 in C24, resulting in 0 in G24 rather than -181.

@Riny_van_Eekelen 

Thanks for the reply. requires more help.

 

The percentage in C24 remains static. If we increase or decrease expense the percentage (in C24)should change accordingly and balance column should not go negative

I am using the autosum and when I add a bunch of numbers the result is always zero why

@BABA1978 Perhaps you can start by explaining the logic for the changes in C. Currently, the uplifts are hardcoded in every cell and are far from static (starting with 3.5, then 9.9 in month 12, thereafter 3.6 but then in month 21 it goes down to 3.5 again. Then 9.5 in month 24 and then 5.9 from month 25 onwards etc. etc. Could it be that you simply forgot to change the factors for months 21 through 23?

 

In the expense column you have amounts with a note every 12th month that mentions an uplift for inflation by 5% (plus 60K for each 12th month).

 

If you want to automate anything, it's important that you clearly define the rules for it first. These can probably be structured in a table that allows you to pick-up the correct percentages, inflation adjustments and yearly extra expenses. Just guessing though.