Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 278075 times)

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 2161
  • Total likes: 364
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1600 on: July 06, 2023, 11:56:30 AM »
only works for the 3rd row but not for the forth and on.
What is the exact formula you have now in the 4th row?

Offline joe1234

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2015
  • Posts: 2061
  • Total likes: 525
  • DansDeals.com Hat Tips 19
    • View Profile
Re: Excel Help and Problems
« Reply #1601 on: July 06, 2023, 11:58:25 AM »
What is the exact formula you have now in the 4th row?
don't know. how do I figure that out?
edit: it shows nothing.

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 2161
  • Total likes: 364
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1602 on: July 06, 2023, 11:59:55 AM »
don't know. how do I figure that out?
When you select a cell, you can see/copy the formula from the formula bar on top of the sheet. Or double-click in the cell to be able to see/copy the formula.
edit: it shows nothing.
Didn't realize I need to specify it again, as seen in my screen recording earlier, after entering the formula in D3 you need to drag fill down the would column.

Offline joe1234

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2015
  • Posts: 2061
  • Total likes: 525
  • DansDeals.com Hat Tips 19
    • View Profile
Re: Excel Help and Problems
« Reply #1603 on: July 06, 2023, 12:01:42 PM »
When you select a cell, you can see/copy the formula from the formula bar on top of the sheet. Or double-click in the cell to be able to see/copy the formula.Didn't realize I need to specify it again, as seen in my screen recording earlier, after entering the formula in D3 you need to drag fill down the would column.
I double clicked and it shows nothing on the 4th row.

Offline joe1234

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2015
  • Posts: 2061
  • Total likes: 525
  • DansDeals.com Hat Tips 19
    • View Profile
Re: Excel Help and Problems
« Reply #1604 on: July 06, 2023, 12:09:09 PM »
Didn't realize I need to specify it again, as seen in my screen recording earlier, after entering the formula in D3 you need to drag fill down the would column.
I did that, but that only helped for the 4th row but not the 5th. Then I pressed enter and after every enter it took care of one one row. also when i put in the payments for example -20 it still added instead of minusing.

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 2161
  • Total likes: 364
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1605 on: July 06, 2023, 12:18:29 PM »
I did that, but that only helped for the 4th row but not the 5th. Then I pressed enter and after every enter it took care of one one row. also when i put in the payments for example -20 it still added instead of minusing.
You did what? Dragged it down the column further than the 5th row and it didn't fill it?



As for the minus, in my new example (since the columns are separate) I used positive numbers to represent the amount deposited. So 20 instead of -20. If you want to use -20 you need to do in cell D2
Code: [Select]
=SUM(B2:C2)Then in D3 use this formula to continue the running totals for all rows:
Code: [Select]
=IF(AND(B3="",C3=""),"",SUM(D2,B3:C3))

Online Yo ssi

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Aug 2019
  • Posts: 6531
  • Total likes: 2490
  • DansDeals.com Hat Tips 59
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1606 on: July 06, 2023, 02:40:38 PM »
Wow kudos @Yisroel Tech for your patience!
_    ,
' )  /
 /  / __   _   _   o
(__/_(_)  /_)_/_)_<_
 //
(/

Offline joe1234

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2015
  • Posts: 2061
  • Total likes: 525
  • DansDeals.com Hat Tips 19
    • View Profile
Re: Excel Help and Problems
« Reply #1607 on: July 06, 2023, 04:49:34 PM »
@Yisroel Tech it works great! thanks so much for all your help and patience!

Offline yoruel

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: May 2013
  • Posts: 764
  • Total likes: 9
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1608 on: July 10, 2023, 03:35:01 PM »
You did what? Dragged it down the column further than the 5th row and it didn't fill it?



As for the minus, in my new example (since the columns are separate) I used positive numbers to represent the amount deposited. So 20 instead of -20. If you want to use -20 you need to do in cell D2
Code: [Select]
=SUM(B2:C2)Then in D3 use this formula to continue the running totals for all rows:
Code: [Select]
=IF(AND(B3="",C3=""),"",SUM(D2,B3:C3))

@Yisroel Tech  how do share/ video the screen.to DDF

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 2161
  • Total likes: 364
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1609 on: July 10, 2023, 03:37:33 PM »
@Yisroel Tech  how do share/ video the screen.to DDF
I screen record as GIF using ShareX (one of the most wonderful tools I found, it's loaded with goodies!) and then upload like any picture.

Offline avadah

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2012
  • Posts: 2496
  • Total likes: 38
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Jerusalem
Re: Excel Help and Problems
« Reply #1610 on: July 11, 2023, 04:01:02 PM »
Wouldn't the much simpler this do the same? Seems to be working as expected for me:
Code: [Select]
=SUM(IF((A:A="apple")+(A:A="orange")+(B:B="apple")+(B:B="orange"), C:C, 0))
BTW, CSE for array formulas aren't needed anymore with newer Excel versions that use Dynamic Array Formulas.
I tried it and keep getting the "Not trying to type a formula?" error

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12811
  • Total likes: 3259
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1611 on: July 11, 2023, 04:02:32 PM »
I tried it and keep getting the "Not trying to type a formula?" error
did you try the DSUM I posted above?
Workflowy. You won't know what you're missing until you try it.

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 2161
  • Total likes: 364
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1612 on: July 11, 2023, 04:21:53 PM »
I tried it and keep getting the "Not trying to type a formula?" error
Which version of Excel do you use?

Also possible that your system "list separator" isn't a comma, then you need to change the commas in the formula to whatever list separator your system is using (you can see it in "Change date, time, or number formats" > Additional Settings, the List separator) or change Excel not to use the system one.

Offline avadah

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2012
  • Posts: 2496
  • Total likes: 38
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Jerusalem
Re: Excel Help and Problems
« Reply #1613 on: July 11, 2023, 04:28:07 PM »


Code: [Select]
=DSUM(A:C,3,H1:I5)
Basically, with DSUM, conditions on the same row are combined with AND, and separate rows are combined with OR.
It's working but i don't understand it. What's the 3 in field doing? I'd prefer a different way bec I don't want those extra cells visible and if I hide them somewhere i'm gonna forget about them.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12811
  • Total likes: 3259
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1614 on: July 11, 2023, 04:30:06 PM »
It's working but i don't understand it. What's the 3 in field doing? I'd prefer a different way bec I don't want those extra cells visible and if I hide them somewhere i'm gonna forget about them.
which 3?
you can put those other cells in a different sheet in the file so you don't have to see them
Workflowy. You won't know what you're missing until you try it.

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 2161
  • Total likes: 364
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1615 on: July 11, 2023, 04:33:23 PM »
which 3?
He's asking about the 3 in the function's 2nd argument:
=DSUM(A:C,3,H1:I5)

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12811
  • Total likes: 3259
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1616 on: July 11, 2023, 04:34:08 PM »
He's asking about the 3 in the function's 2nd argument:
=DSUM(A:C,3,H1:I5)
Oh
It means that the column to sum is the 3rd column in the range (1st argument)
Workflowy. You won't know what you're missing until you try it.

Offline avadah

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2012
  • Posts: 2496
  • Total likes: 38
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Jerusalem
Re: Excel Help and Problems
« Reply #1617 on: July 11, 2023, 04:43:06 PM »
Oh
It means that the column to sum is the 3rd column in the range (1st argument)
Got it. Why does it need to alternate lines? Meaning have apple in fruit column next to apple in food column? How would I use it with the wild card feature meaning it would count apple, green apple and red apple?
« Last Edit: July 11, 2023, 04:46:38 PM by avadah »

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12811
  • Total likes: 3259
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1618 on: July 11, 2023, 04:46:45 PM »
Got it. Why does it need to alternate lines? Meaning have apple in fruit column next to apple in food column?
if they were in the same row it would join the criteria with AND, so it'd only find those that have apple in both, or orange in both. Separate rows tells it to join the criteria with OR so it'll run for any row that has apple in either column, or orange in either column
Workflowy. You won't know what you're missing until you try it.

Offline avadah

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2012
  • Posts: 2496
  • Total likes: 38
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Jerusalem
Re: Excel Help and Problems
« Reply #1619 on: July 11, 2023, 04:55:39 PM »
if they were in the same row it would join the criteria with AND, so it'd only find those that have apple in both, or orange in both. Separate rows tells it to join the criteria with OR so it'll run for any row that has apple in either column, or orange in either column
Thanks. Is there a way to use it with the wild card feature?