SOLVED
Home

using multiple IFs with result as calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-837185%22%20slang%3D%22en-US%22%3Eusing%20multiple%20IFs%20with%20result%20as%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-837185%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI%20am%20working%20on%20the%20following%20formula%20but%20keep%20getting%20a%20null%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(raw_data!%24B%242%3A%24B%241698%3D%24A18%2C%20raw_data!%24BA%242%3A%24BA%241698%3DB%2417)%2C%20MEDIAN(raw_data!%24AY%242%3A%24AY%241698)%2C%20%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20is%20drawing%20from%20a%20worksheet%20with%20data%20on%20archaeological%20ceramics%20(I%20am%20a%20graduate%20student%20in%20archaeology).%20I%20want%20to%20calculate%20the%20median%20for%20values%20in%20column%20AY%20(a%20measurement%20value)%20IF%20the%20ceramic%20vessel%20associated%20with%20the%20measurement%20meets%20criteria%20coded%20in%20two%20other%20columns%20(B%20and%20BA).%20In%20this%20case%2C%20only%20if%20they%20are%20a%20certain%20shape%20and%20from%20a%20certain%20unit.%20So%2C%20I%20want%20to%20calculate%20the%20median%20measurement%20for%20all%20bowls%20from%20Unit%202%2C%20for%20example.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hit%20control-shift-enter%20to%20make%20it%20an%20array%20formula%2C%20but%20still%20no%20dice.%20Cannot%20for%20the%20life%20of%20me%20figure%20out%20what%20is%20wrong.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-837185%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-837251%22%20slang%3D%22en-US%22%3ERe%3A%20using%20multiple%20IFs%20with%20result%20as%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-837251%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403466%22%20target%3D%22_blank%22%3E%40AJ_Smith%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDid%20you%20try%20it%20like%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMEDIAN(IF(raw_data!%24B%242%3A%24B%241698%3D%24A18%2CIF(%20raw_data!%24BA%242%3A%24BA%241698%3DB%2417%2Craw_data!%24AY%242%3A%24AY%241698)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-837265%22%20slang%3D%22en-US%22%3ERe%3A%20using%20multiple%20IFs%20with%20result%20as%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-837265%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20response.%20I%20tried%20your%20suggestion%20but%20now%20get%20a%20%23NUM!%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-837293%22%20slang%3D%22en-US%22%3ERe%3A%20using%20multiple%20IFs%20with%20result%20as%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-837293%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BOk%2C%20I%20tried%20entering%20your%20formula%20into%20a%20different%20cell%20and%20it%20seems%20to%20work!%20I%20think%20the%20column%20where%20I%20was%20entering%20the%20formula%20became%20formatted%20strangely--if%20I%20tried%20to%20change%20one%20cell%2C%20I%20would%20get%20an%20error%20message%20saying%20%22you%20can't%20change%20part%20of%20an%20array.%22%20Not%20sure%20how%20that%20happened%2C%20but%20starting%20fresh%20seemed%20to%20get%20rid%20of%20the%20troublesome%20formatting.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank-you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-837320%22%20slang%3D%22en-US%22%3ERe%3A%20using%20multiple%20IFs%20with%20result%20as%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-837320%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403466%22%20target%3D%22_blank%22%3E%40AJ_Smith%3C%2FA%3E!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
AJ_Smith
New Contributor

Hi all,

I am working on the following formula but keep getting a null result.

 

=IF(AND(raw_data!$B$2:$B$1698=$A18, raw_data!$BA$2:$BA$1698=B$17), MEDIAN(raw_data!$AY$2:$AY$1698), "")

 

The formula is drawing from a worksheet with data on archaeological ceramics (I am a graduate student in archaeology). I want to calculate the median for values in column AY (a measurement value) IF the ceramic vessel associated with the measurement meets criteria coded in two other columns (B and BA). In this case, only if they are a certain shape and from a certain unit. So, I want to calculate the median measurement for all bowls from Unit 2, for example. 

 

I hit control-shift-enter to make it an array formula, but still no dice. Cannot for the life of me figure out what is wrong. 

 

 

4 Replies
Solution

@AJ_Smith 

Did you try it like this?

 

=MEDIAN(IF(raw_data!$B$2:$B$1698=$A18,IF( raw_data!$BA$2:$BA$1698=B$17,raw_data!$AY$2:$AY$1698)))

@Subodh_Tiwari_sktneer Thanks for the response. I tried your suggestion but now get a #NUM! error.

@Subodh_Tiwari_sktneer Ok, I tried entering your formula into a different cell and it seems to work! I think the column where I was entering the formula became formatted strangely--if I tried to change one cell, I would get an error message saying "you can't change part of an array." Not sure how that happened, but starting fresh seemed to get rid of the troublesome formatting. 

 

Thank-you!

Highlighted

You're welcome @AJ_Smith! Glad it worked as desired.

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies