SOLVED
Home

Excel Formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-482992%22%20slang%3D%22en-US%22%3EExcel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482992%22%20slang%3D%22en-US%22%3E%3CP%3EExcel%202013%3A%20I%20want%20a%20cell%20to%20be%20blank%20or%20have%20a%20value%20based%20on%20data%2Fno%20data%20in%20a%20specific%20cell%20and%20data%2Fno%20data%20in%20a%20range%20of%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20in%20cell%20AJ7%20I%20want%20either%20a%20numerical%20value%20or%20no%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20B7%20has%20either%20a%20name%20or%20no%20name%20(however%20the%20cell%20has%20a%20formula%20associated%20with%20it).%20It%20gets%20populated%20with%20Names%20from%20another%20sheet%20in%20the%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECells%20C7%3AAG7%20either%20have%20data%20(A%2C%20N%2C%20L%2C%20V%2C%20or%20H)%20or%20no%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20Cell%20B7%20has%20data%20and%20Cells%20C7%3AAG7%20have%20no%20data%3A%20Cell%20AJ7%20needs%20to%20have%20a%20numerical%20value%20of%20(.5).%3C%2FP%3E%3CP%3EIf%20Cell%20B7%20has%20data%20and%20Cells%20C7%3AAG7%20have%20data%3A%20Cell%20AJ7%20needs%20to%20have%20a%20value%20of%20(0).%3C%2FP%3E%3CP%3EIf%20Cell%20B7%20has%20no%20data%20and%20Cells%20C7%3AAG7%20have%20no%20data%3A%20Cell%20AJ7%20needs%20to%20have%20no%20value.%3C%2FP%3E%3CP%3E-----------------------------------------------------------------------------------------------------------------I%20can%20get%20a%20value%20in%20AJ7%20if%20I%20use%20COUNTA%20for%20the%20range%20of%20Cells%20A7%3AAG7%20but%20if%20I%20add%20a%20variable%20for%20Cell%20B7%20(or%20Sheet2!B7)%20I%20generally%20get%20notified%20that%20the%20formula%20is%20not%20a%20formula.%20I%20have%20tried%20to%20add%20%22AND%22%20but%20no%20work.%3C%2FP%3E%3CP%3E-----------------------------------------------------------------------------------------------------------------Formulas%20that%20produce%20a%20value%20in%20AJ(x)%2C%20but%20do%20not%20include%20the%20secondary%20variable%20--%3C%2FP%3E%3CP%3E%3DIF(COUNTA(C7%3AAG7)%3D0%2C%20%22.5%22%2C%220%22)%3C%2FP%3E%3CP%3E%3DIF(COUNTA('Employee%20Names'!B7)%3D0%2C%220%22%2C%22.5%22)%3C%2FP%3E%3CP%3E-----------------------------------------------------------------------------------------------------------------Formula%20with%20%22AND%22%20that%20does%20not%20work%3A%20(you've%20entered%20too%20many%20arguments)%3C%2FP%3E%3CP%3E%3DIF(AND(COUNTA('Employee%20Names'!B7%3D%221%22))%2CC7%3AAG7%2C%220%22%2C%22.5%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-482992%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-483048%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-483048%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%3EI%20have%20already%20tried%20that%20formula%20and%20it%20doesn't%20work%20either.%20I%20have%20updated%20my%20post%20with%20additional%20info%20and%20a%20copy%20of%20the%20spreadsheet.%20Detlef%20Lewin%20provided%20a%20formula%20that%20does%20what%20I%20am%20looking%20for%2C%20but%20it%20breaks%20Columns%20AF%2FAG%20(causes%20a%20%23VALUE!)%20to%20return%20into%20the%20empty%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-483044%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-483044%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20works%20with%20the%20exception%20it%20causes%20a%20%23VALUE!%20in%20the%20remaining%20rows%20columns%20AF%2FAG.%3C%2FP%3E%3CP%3ESee%20the%20attached%20image.%20Also%2C%20I%20attached%20my%20workbook%20to%20the%20post...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-483029%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-483029%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20adding%20my%20spreadsheet.%3C%2FP%3E%3CP%3EIf%20there%20is%20no%20name%20in%20the%20Employee%20Name%20Column%2C%20then%20there%20would%20be%20no%20selections%20in%20C7%3AAG7%20therefor%20there%20should%20be%20no%20value%20or%20a%20value%20of%200%20in%20Perfect%20Attendance%20but%20the%20value%20should%20be%20hidden.%3C%2FP%3E%3CP%3EIf%20there%20is%20a%20name%20in%20the%20Employee%20Name%20Column%2C%20and%20no%20selections%20in%20C7%3AAG7%2C%20then%20there%20should%20be%20a%20value%20of%20.5%20in%20the%20Perfect%20Attendance%20Column.%3C%2FP%3E%3CP%3EIf%20there%20is%20a%20name%20in%20the%20Employee%20Name%20Column%2C%20and%20either%20V%2C%20N%2C%20A%2C%20L%2C%20or%20H%20selected%20in%20C7%3AAG7%2C%20then%20there%20should%20be%20no%20value%20or%20a%20value%20of%200%20in%20the%20Perfect%20Attendance%20Column%20and%20visible.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20premise%20here%20is%3A%20If%20they%20have%20perfect%20attendance%20for%20the%20month%2C%20they%20get%20a%20deduction%20of%20.5%20from%20their%20%22Total%20Accrued%20Points%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-483011%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-483011%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327673%22%20target%3D%22_blank%22%3E%40RRCIT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20should%20work.%3C%2FP%3E%3CPRE%3E%3DIF(B7%26lt%3B%26gt%3B%22%22%2CIF(COUNTA(C7%3AAG7)%3D0%2C0.5%2C0)%2C%22%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-483010%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-483010%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327673%22%20target%3D%22_blank%22%3E%40RRCIT%3C%2FA%3E%26nbsp%3B%2C%20it's%20too%20hard%20to%20understand%20what%20is%20the%20logic%20behind%20without%20the%20sample%20file.%20Regarding%20the%20last%20formula%3C%2FP%3E%0A%3CPRE%3E%3DIF(AND(COUNTA('Employee%20Names'!B7%3D%221%22))%2CC7%3AAG7%2C%220%22%2C%22.5%22)%3C%2FPRE%3E%0A%3CP%3EWithin%20it%26nbsp%3B'Employee%20Names'!B7%3D%221%22%20returns%20TRUE%20or%20FALSE%2C%20COUNTA%20on%20it%20will%20be%20always%201%20since%20it%20always%20have%20one%20%22not%20empty%22%20argument.%3C%2FP%3E%0A%3CP%3ENext%2C%20you%20have%20AND(1)%20which%20always%20return%20TRUE.%20Thus%20your%20formula%20is%3C%2FP%3E%0A%3CPRE%3E%3DIF(TRUE%2CC7%3AAG7%2C%220%22%2C%22.5%22)%3C%2FPRE%3E%0A%3CP%3EThat%20doesn't%20work%20-%20you%20have%204%20arguments%20instead%20of%203%20required.%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20mean%3C%2FP%3E%0A%3CPRE%3E%3DIF(AND(%20('Employee%20Names'!B7%3D1)%2C%20(COUNTA(C7%3AAG7)%3D1)%20)%20%2C0%20%2C0.5)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-483983%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-483983%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327673%22%20target%3D%22_blank%22%3E%40RRCIT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20that%20I%20know%20you%20workbook%20the%20formula%20should%20be%3A%3C%2FP%3E%3CPRE%3E%3DIF(B7%26lt%3B%26gt%3B0%2CIF(COUNTA(C7%3AAG7)%3D0%2C0.5%2C0)%2C%22%22)%3C%2FPRE%3E%3CP%3EUsing%20a%20direct%20reference%20to%20another%20worksheet%20is%20a%20very%20bad%20idea.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20for%20the%20%23VALUE!%20error%3A%20You%20wanted%20a%20formula%20to%20produce%20a%20'blank'%20which%20it%20does.%3C%2FP%3E%3CP%3EYour%20formula%20in%20'January'%20column%20H%20is%20essentially%3A%3C%2FP%3E%3CPRE%3E%3DNumber%20-%20AJ7%3C%2FPRE%3E%3CP%3E%26nbsp%3BBut%20when%20AJ7%20is%20'blank%22%20it%20is%20text.%20And%20Number-Text%20does%20not%20compute%20in%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487018%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487018%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20Can%20I%20make%20the%20value%20of%20AJ7%20be%200%20if%20there%20is%20no%20data%20in%20B7...%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20then%20use%20conditional%20formatting%20to%20hide%20the%200....%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487547%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487547%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327673%22%20target%3D%22_blank%22%3E%40RRCIT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20revised%20formula%20for%20AJ7%20would%20be%3A%3C%2FP%3E%3CPRE%3E%3D(COUNTA(C7%3AAG7)%3D0)*(B7%26lt%3B%26gt%3B0)*0.5%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487956%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487956%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcellent.%20Works%20Perfect.%20Don't%20even%20need%20to%20do%20Conditional%20Formatting.%20Than%20You.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-504994%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-504994%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20works%20great.%20Unfortunately%2C%20I%20have%20run%20into%20another%20issue.%20This%20needs%20to%20be%20conditional%20on%20an%20employee%20hire%20date.%20I%20have%20added%20a%20new%20column%20(Start%20Date)%20to%20the%20Employee%20Names%20sheet.%20Employees%20can't%20accrue%20Perfect%20Attendance%20points%20prior%20to%20their%20%22Start%20Date%22.%20So%20if%20the%20employee%20starts%20in%20April...%20then%20Jan%20-%20Mar%20would%20not%20have%20points%20in%20the%20Perfect%20Attendance%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-505456%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-505456%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327673%22%20target%3D%22_blank%22%3E%40RRCIT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20understand%20the%20problem.%3C%2FP%3E%3CP%3EPerhaps%20you%20could%20add%20some%20data%20to%20illustrate%20the%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-506014%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-506014%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOK.%20I%20have%20entered%20Employee%201%20and%20Employee%202%3A%3C%2FP%3E%3CP%3EEmployee%201%20Started%206%2F1%2F2108.%3C%2FP%3E%3CP%3EThey%20get%20.5%20points%20for%20each%20month%20that%20they%20have%20perfect%20attendance.%3C%2FP%3E%3CP%3ESo%201.5%20points%20total%20for%20Jan%2FFeb%2FMar.%3C%2FP%3E%3CP%3EIf%20they%20receive%20an%20attendance%20violation%20in%20Apr%20--%20the%20violation%20gets%20reduced%20by%20the%20accrued%20perfect%20attendance%20points.%3C%2FP%3E%3CP%3EEmployee%202%20Started%203%2F1%2F2019.%3C%2FP%3E%3CP%3EThey%20get%20.5%20points%20for%20each%20month%20that%20they%20have%20perfect%20attendance.%3C%2FP%3E%3CP%3EThey%20had%20perfect%20attendance%20in%20Mar.%3C%2FP%3E%3CP%3ESo%20.5%20points%20total%20for%20Mar.%3C%2FP%3E%3CP%3EIf%20Employee%202%20receives%20an%20attendance%20violation%20in%20Apr%20--%20the%20violation%20gets%20reduced%20by%20the%20accrued%20perfect%20attendance%20points.%3C%2FP%3E%3CP%3EHowever%2C%20since%20employee%202%20was%20not%20employed%20in%20Jan%2FFeb%2C%20they%20are%20not%20entitled%20to%20perfect%20attendance%20points%20for%20Jan%2FFeb.%3C%2FP%3E%3CP%3ESo%20-%20we%20need%20to%20modify%20our%20formula%20so%20that%20it%20does%20not%20award%20perfect%20attendance%20points%20for%20the%20months%20that%20they%20weren't%20employed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20spreadsheet%20with%20data.%20As%20you%20can%20see%20--%20Empl%202%20in%20April%20has%20accrued%20points%20of%20.5%20(the%20same%20as%20Empl%201)%20however%2C%20it%20should%20be%201.5%20since%20Jan%2FFeb%20should%20be%200%20for%20Perfect%20Attendance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20that%20the%20workbook%20that%20is%20provided%20to%20my%20managers%20has%20most%20of%20the%20cells%20protected.%20The%20days%20have%20drop%20down%20selections%20and%20Employee%20Name%2FPoints%20Brought%20Forward%2FStart%20Date%20are%20the%20only%20cells%20that%20are%20unprotected.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-507226%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-507226%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327673%22%20target%3D%22_blank%22%3E%40RRCIT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3D((COUNTA(C7%3AAG7)%3D0)*(B7%26lt%3B%26gt%3B0)*0%2C5)*(--(1%26amp%3BMonthName%26amp%3BCalendarYear)%26gt%3B%3DVLOOKUP(%5B%40%5BEmployee%20Name%5D%5D%2C'Employee%20Names'!%24A%242%3A%24C%243%2C3%2CFALSE))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-507901%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-507901%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20get%20an%20error%3A%20See%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508372%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508372%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327673%22%20target%3D%22_blank%22%3E%40RRCIT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20the%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508579%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508579%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOK.%20I%20see%20it%20seems%20to%20work%20however%2C%20I%20don't%20quite%20get%20this%20part%3A%26nbsp%3B'Employee%20Names'!%24A%242%3A%24C%243%2C3%2CFALSE))%20..%3C%2FP%3E%3CP%3ECan%20you%20break%20it%20down%20for%20me%3F%20I%20would%20think%20it%20should%20be%20%24A%242%3A%24C%242%2C%20for%20Employee%201%20and%3C%2FP%3E%3CP%3E%24A%243%3A%24C%243%2C%20for%20Employee%202....%20and%20what%20is%20the%20%2C3%2C%20prior%20to%20the%20FALSE%20referring%20to%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20now%20there%20is%20a%20%23N%2FA%20in%20the%20remaining%20cells%20for%20columns%20AI%20and%20AJ%20where%20there%20are%20no%20employees.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20quite%20sure%20why%20my%20copy%20and%20paste%20didn't%20work....%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508592%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508592%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327673%22%20target%3D%22_blank%22%3E%40RRCIT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20the%20months%20following%20the%20hire%20date%20April%20-%20Dec%20are%20not%20accruing%20perfect%20attendance%20points%20for%20Employee%202%20and%20should%20be....%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508683%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508683%22%20slang%3D%22en-US%22%3EThis%20seems%20to%20be%20a%20perpetual%20conversation.%20I%20hereby%20admonish%20you%20to%20start%20a%20new%20one%2C%20given%20that%20you%20terminated%20this%20yourself.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508745%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508745%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327673%22%20target%3D%22_blank%22%3E%40RRCIT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20That%20is%20a%20simple%20VLOOKUP().%20Did%20you%20never%20use%20a%20VLOOKUP()%20before%3F%20Take%20a%20look%20at%20the%20online%20help%20page.%3C%2FP%3E%3CP%3E2.%20When%20there%20is%20no%20employee%20then%20delete%20the%20row.%20Simple%20as%20that.%3C%2FP%3E%3CP%3E3.%20I%20was%20afraid%20this%20is%20getting%20more%20and%20more%20complex.%20With%20the%20current%20setup%20I%20will%20not%20be%20able%20to%20provide%20good%20solutions.%20Change%20your%20setup%20into%20a%20record%20list.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508786%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508786%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20all%20your%20help.%20This%20is%20way%20beyond%20my%20knowledge%20level%20and%20I%20am%20getting%20blasted%20for%20continuing%20the%20conversation.%20I%20have%20a%20workaround%20that%20I%20can%20implement.%20I%20don't%20know%20how%20to%20close%20a%20conversation%2C%20but%20I%20won't%20be%20adding%20any%20more%20to%20this.%20Again%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-833577%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-833577%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sir%2C%3C%2FP%3E%3CP%3ERecently%20we%20have%20upgraded%20to%20office%20365.%20After%20upgradation%20we%20are%20unable%20to%20use%20Erlang%20B%20formula%26nbsp%3B%3CFONT%3Enpxcel32%3C%2FFONT%3E.%20Please%20provide%20your%20suggestion%20how%20to%20make%20to%20make%20it%20workable.%20Thank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERony%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
RRCIT
Occasional Contributor

Excel 2013: I want a cell to be blank or have a value based on data/no data in a specific cell and data/no data in a range of cells.

 

So in cell AJ7 I want either a numerical value or no value.

 

Cell B7 has either a name or no name (however the cell has a formula associated with it). It gets populated with Names from another sheet in the workbook.

 

Cells C7:AG7 either have data (A, N, L, V, or H) or no data.

 

If Cell B7 has data and Cells C7:AG7 have no data: Cell AJ7 needs to have a numerical value of (.5).

If Cell B7 has data and Cells C7:AG7 have data: Cell AJ7 needs to have a value of (0).

If Cell B7 has no data and Cells C7:AG7 have no data: Cell AJ7 needs to have no value.

-----------------------------------------------------------------------------------------------------------------I can get a value in AJ7 if I use COUNTA for the range of Cells A7:AG7 but if I add a variable for Cell B7 (or Sheet2!B7) I generally get notified that the formula is not a formula. I have tried to add "AND" but no work.

-----------------------------------------------------------------------------------------------------------------Formulas that produce a value in AJ(x), but do not include the secondary variable --

=IF(COUNTA(C7:AG7)=0, ".5","0")

=IF(COUNTA('Employee Names'!B7)=0,"0",".5")

-----------------------------------------------------------------------------------------------------------------Formula with "AND" that does not work: (you've entered too many arguments)

=IF(AND(COUNTA('Employee Names'!B7="1")),C7:AG7,"0",".5")

21 Replies

@RRCIT , it's too hard to understand what is the logic behind without the sample file. Regarding the last formula

=IF(AND(COUNTA('Employee Names'!B7="1")),C7:AG7,"0",".5")

Within it 'Employee Names'!B7="1" returns TRUE or FALSE, COUNTA on it will be always 1 since it always have one "not empty" argument.

Next, you have AND(1) which always return TRUE. Thus your formula is

=IF(TRUE,C7:AG7,"0",".5")

That doesn't work - you have 4 arguments instead of 3 required.

Perhaps you mean

=IF(AND( ('Employee Names'!B7=1), (COUNTA(C7:AG7)=1) ) ,0 ,0.5)

@RRCIT 

This should work.

=IF(B7<>"",IF(COUNTA(C7:AG7)=0,0.5,0),"")

 

I am adding my spreadsheet.

If there is no name in the Employee Name Column, then there would be no selections in C7:AG7 therefor there should be no value or a value of 0 in Perfect Attendance but the value should be hidden.

If there is a name in the Employee Name Column, and no selections in C7:AG7, then there should be a value of .5 in the Perfect Attendance Column.

If there is a name in the Employee Name Column, and either V, N, A, L, or H selected in C7:AG7, then there should be no value or a value of 0 in the Perfect Attendance Column and visible.

 

The premise here is: If they have perfect attendance for the month, they get a deduction of .5 from their "Total Accrued Points"

@Detlef Lewin 

 

This formula works with the exception it causes a #VALUE! in the remaining rows columns AF/AG.

See the attached image. Also, I attached my workbook to the post...

 

@Sergei Baklan 

I have already tried that formula and it doesn't work either. I have updated my post with additional info and a copy of the spreadsheet. Detlef Lewin provided a formula that does what I am looking for, but it breaks Columns AF/AG (causes a #VALUE!) to return into the empty cells.

@RRCIT 

Now that I know you workbook the formula should be:

=IF(B7<>0,IF(COUNTA(C7:AG7)=0,0.5,0),"")

Using a direct reference to another worksheet is a very bad idea.

 

As for the #VALUE! error: You wanted a formula to produce a 'blank' which it does.

Your formula in 'January' column H is essentially:

=Number - AJ7

 But when AJ7 is 'blank" it is text. And Number-Text does not compute in Excel.

 

@Detlef Lewin 

So, Can I make the value of AJ7 be 0 if there is no data in B7... 

I can then use conditional formatting to hide the 0....

Solution

@RRCIT 

 

The revised formula for AJ7 would be:

=(COUNTA(C7:AG7)=0)*(B7<>0)*0.5

@Detlef Lewin 

Excellent. Works Perfect. Don't even need to do Conditional Formatting. Than You.

@Detlef Lewin 

The formula works great. Unfortunately, I have run into another issue. This needs to be conditional on an employee hire date. I have added a new column (Start Date) to the Employee Names sheet. Employees can't accrue Perfect Attendance points prior to their "Start Date". So if the employee starts in April... then Jan - Mar would not have points in the Perfect Attendance column.

@RRCIT 

I don't understand the problem.

Perhaps you could add some data to illustrate the problem.

 

@Detlef Lewin 

OK. I have entered Employee 1 and Employee 2:

Employee 1 Started 6/1/2108.

They get .5 points for each month that they have perfect attendance.

So 1.5 points total for Jan/Feb/Mar.

If they receive an attendance violation in Apr -- the violation gets reduced by the accrued perfect attendance points.

Employee 2 Started 3/1/2019.

They get .5 points for each month that they have perfect attendance.

They had perfect attendance in Mar.

So .5 points total for Mar.

If Employee 2 receives an attendance violation in Apr -- the violation gets reduced by the accrued perfect attendance points.

However, since employee 2 was not employed in Jan/Feb, they are not entitled to perfect attendance points for Jan/Feb.

So - we need to modify our formula so that it does not award perfect attendance points for the months that they weren't employed.

 

I have attached a spreadsheet with data. As you can see -- Empl 2 in April has accrued points of .5 (the same as Empl 1) however, it should be 1.5 since Jan/Feb should be 0 for Perfect Attendance.

 

Note that the workbook that is provided to my managers has most of the cells protected. The days have drop down selections and Employee Name/Points Brought Forward/Start Date are the only cells that are unprotected.

@RRCIT 

 

=((COUNTA(C7:AG7)=0)*(B7<>0)*0,5)*(--(1&MonthName&CalendarYear)>=VLOOKUP([@[Employee Name]],'Employee Names'!$A$2:$C$3,3,FALSE))

 

@Detlef Lewin 

I get an error: See attached.

 

@RRCIT 

I attached the file.

 

@Detlef Lewin 

OK. I see it seems to work however, I don't quite get this part: 'Employee Names'!$A$2:$C$3,3,FALSE)) ..

Can you break it down for me? I would think it should be $A$2:$C$2, for Employee 1 and

$A$3:$C$3, for Employee 2.... and what is the ,3, prior to the FALSE referring to?

 

Also, now there is a #N/A in the remaining cells for columns AI and AJ where there are no employees.

 

Not quite sure why my copy and paste didn't work....

@RRCIT 

Also the months following the hire date April - Dec are not accruing perfect attendance points for Employee 2 and should be....

This seems to be a perpetual conversation. I hereby admonish you to start a new one, given that you terminated this yourself.

@RRCIT 

1. That is a simple VLOOKUP(). Did you never use a VLOOKUP() before? Take a look at the online help page.

2. When there is no employee then delete the row. Simple as that.

3. I was afraid this is getting more and more complex. With the current setup I will not be able to provide good solutions. Change your setup into a record list.

@Detlef Lewin 

Thanks for all your help. This is way beyond my knowledge level and I am getting blasted for continuing the conversation. I have a workaround that I can implement. I don't know how to close a conversation, but I won't be adding any more to this. Again thanks.

Hi Sir,

Recently we have upgraded to office 365. After upgradation we are unable to use Erlang B formula npxcel32. Please provide your suggestion how to make to make it workable. Thank you.

 

Rony 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
12 Replies