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
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies