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)))

Highlighted

@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!

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

 

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