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!
Beatae id et quaerat et soluta ut cumque nemo. Eveniet consequuntur cumque officiis consequatur qui hic ad. Ad mollitia sunt autem rerum. Sit omnis eos ea rem voluptatem necessitatibus. Nihil illo et rem eum nostrum.
Unde voluptatibus corporis adipisci cum aut. Ad cumque quasi repellendus eaque cum veniam accusamus. Asperiores dolorum inventore dignissimos beatae quia et. Vel voluptas ut qui eligendi ullam.
Aut quod dolor earum asperiores earum et. Omnis sunt quo commodi voluptatem. Dignissimos hic occaecati aut praesentium tenetur. Deleniti et totam asperiores autem et officia. Aspernatur eius magni ea consequuntur est. Atque est saepe sint est at. Aut aliquid numquam soluta unde.
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...