Hey guys - so I'm trying to find the average return on natural gas that my firm trades at certain domestic hubs and have a spreadsheet with thousands of rows of data and only some of it is actual volumes of natural gas given that for some reason the sheet shows dollar amounts on 0 volume which are erroneous entries that the trading log is spitting out or they're based on deals structured in specific ways that I don't need to include in my model.
So for my question: If for example - the volumes are contained in column A and the returns are contained in column B how do I calculate the returns on ONLY the gas contained in column B when column A is greater than 0?
Thanks!
Sumif and Countif In your case, Sumif(A1:An,">0",B1:Bn)/Countif(A1:An,">0") This sums up all of the returns with a volume greater than 0, then divides it by the number of days traded with volumes great than 0 to find the average.
Excellent - thanks!
Aut voluptas aut impedit sit sed assumenda esse cumque. Quia ut quasi officiis delectus. Sit error voluptatem recusandae corporis.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...