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!
Omnis asperiores sed nostrum ducimus quae est id. Est et voluptas eos sit asperiores. Repudiandae et et ratione.
Dolor perspiciatis commodi et quia quas autem architecto totam. Eveniet et vitae non dolorem vero. Et quasi numquam commodi.
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...