topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • December 16, 2018, 05:05 PM
  • Proudly celebrating 13 years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Trickiest EXCEL formula problem ever!  (Read 701 times)

kalos

  • Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 1,691
    • View Profile
    • Donate to Member
Trickiest EXCEL formula problem ever!
« on: September 28, 2018, 10:53 AM »
Hello!

I have a row with dates that are start of weeks ongoing. Below that, I have one row with numbers. Below that, I have a row that I want to fill in with a formula.

In an independent cell, I input a date. Then the formula I want to fill in, checks if the date is future/past of the date at the top row in the same column. If future, it fills the cell with zero.
The problem is what I want it to do if the date is past.

I want to display the ratio of the number above in the middle row and the same column, with respect to the sum of the middle row numbers. The tricky bit is what exact sum I want it to consider.
I want the sum of the first cell in the column that the bottom line cell is not zero, up to the end of the row.

I need somehow to reference and store in a variable that. Or somehow calculate that ratio, considering the right sum, which is fixed once defined, but it is defined by the first cell that gets a non zero value.

I don't want to do this with a macro as I don't know anything about macros. Any help?

Thanks!

Deozaan

  • Charter Member
  • Joined in 2006
  • ***
  • Points: 1
  • Posts: 8,595
    • View Profile
    • The Blog of Deozaan
    • Donate to Member
Re: Trickiest EXCEL formula problem ever!
« Reply #1 on: September 30, 2018, 10:53 PM »
Try asking on a website designed for people to ask questions and get them answered, such as Stack Exchange:

https://meta.stackex...soft-excel-questions

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 9,891
    • View Profile
    • Donate to Member
Re: Trickiest EXCEL formula problem ever!
« Reply #2 on: October 01, 2018, 09:41 AM »
Try asking on a website designed for people to ask questions and get them answered, such as Stack Exchange:

https://meta.stackex...soft-excel-questions
:Thmbsup: