Topic Wiki

Rule 1: Did you try A.I? Right before you click post, give it a quick run. Most excel questions can be answered very quickly, thoroughly, and clearly via chatgpt. Wrong answer, or having trouble with the correct wording of the prompt? Post for help

=================
For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by Jojo202 on March 01, 2024, 12:09:44 AM »

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

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12985
  • Total likes: 3449
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #280 on: March 02, 2015, 04:35:19 PM »
in a sumif, i want to add up all the values for certain states on the list, but im excluding a few. is there a function to nest inside the sumif that i can use for this to list the few im excluding?
thanks
Sounds like DSUM would be a better option.
http://www.techonthenet.com/excel/formulas/dsum.php

Basically, you set up another small table with the criteria you want.
Workflowy. You won't know what you're missing until you try it.

Offline dealfinder85

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 3580
  • Total likes: 9
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #281 on: March 02, 2015, 04:37:45 PM »
Sounds like DSUM would be a better option.
http://www.techonthenet.com/excel/formulas/dsum.php
not sure thats gonna help
i need to include in the function the list of states im excluding, that i dont want to add up those values corresponding to those states

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12985
  • Total likes: 3449
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #282 on: March 02, 2015, 04:38:39 PM »
not sure thats gonna help
i need to include in the function the list of states im excluding, that i dont want to add up those values corresponding to those states
Should be possible. Give me a minute...
Workflowy. You won't know what you're missing until you try it.

Offline dealfinder85

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 3580
  • Total likes: 9
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #283 on: March 02, 2015, 04:43:02 PM »
Should be possible. Give me a minute...
figured out a less technical way to do it
thanks though

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12985
  • Total likes: 3449
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #284 on: March 02, 2015, 04:44:00 PM »
figured out a less technical way to do it
thanks though
NP. I'm curious now, so I'll keep looking :)

BTW you might want to look into sumifs
Workflowy. You won't know what you're missing until you try it.

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5386
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #285 on: March 02, 2015, 04:44:47 PM »
figured out a less technical way to do it
thanks though
Added a new column and deleted the ones you don't want?

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12985
  • Total likes: 3449
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #286 on: March 02, 2015, 04:49:35 PM »
Got it - your small table should look something like this:

State  State  State
<>NY <>NJ  <>CT

etc, for all the states you don't want
Workflowy. You won't know what you're missing until you try it.

Offline dealfinder85

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 3580
  • Total likes: 9
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #287 on: March 02, 2015, 04:54:27 PM »
Got it - your small table should look something like this:

State  State  State
<>NY <>NJ  <>CT

etc, for all the states you don't want
thanks

Offline PTU

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Nov 2014
  • Posts: 289
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #288 on: March 02, 2015, 04:55:08 PM »
in a sumif, i want to add up all the values for certain states on the list, but im excluding a few. is there a function to nest inside the sumif that i can use for this to list the few im excluding?
thanks
just put a minus sign in your formula  by those states?

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5386
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #289 on: March 19, 2015, 09:57:53 AM »
have a few 100K rows and i need to convert a column from text to number
i know if you highlight them then that thing pops up where you can, but that keeps freezing my excel
another option?
Make a cell with the number 1 in it, copy that cell, then do paste special over the entire column, there should be an option to Multiply. Should work.

Offline dealfinder85

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 3580
  • Total likes: 9
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #290 on: March 19, 2015, 10:04:59 AM »
Make a cell with the number 1 in it, copy that cell, then do paste special over the entire column, there should be an option to Multiply. Should work.
thanks

Offline smurf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2010
  • Posts: 4562
  • Total likes: 313
  • DansDeals.com Hat Tips 8
    • View Profile
  • Location: NJ
Re: Excel Problem
« Reply #291 on: March 20, 2015, 01:37:04 AM »
Make a cell with the number 1 in it, copy that cell, then do paste special over the entire column, there should be an option to Multiply. Should work.
use text to columns. Don't select a delimiter and choose the cell type you want.

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2012
  • Posts: 775
  • Total likes: 45
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #292 on: April 30, 2015, 06:50:07 PM »
I overwrote data on an excel sheet how do I restore to a previous version? I right-clicked on the file and there is no previous version saved, any suggestions?
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12985
  • Total likes: 3449
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #293 on: April 30, 2015, 08:42:06 PM »
I overwrote data on an excel sheet how do I restore to a previous version? I right-clicked on the file and there is no previous version saved, any suggestions?
I'm assuming you tried control-z?
Workflowy. You won't know what you're missing until you try it.

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2012
  • Posts: 775
  • Total likes: 45
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #294 on: April 30, 2015, 08:59:36 PM »
I'm assuming you tried control-z?
I saved the wrong data by mistake.
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline menachem_m

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2012
  • Posts: 1219
  • Total likes: 1
  • DansDeals.com Hat Tips 29
    • View Profile
Re: Excel Problem
« Reply #295 on: May 17, 2015, 05:22:27 PM »
Is there any formula to calculate an average daily balance for a set date range? When downloading BOFA data to excel, it only provides a row for days with activity, so I can't just do a simple average function. Or a quick way to 'fill in' all the missing days with the same balance as the previous day?

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5386
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #296 on: May 17, 2015, 07:46:34 PM »
Is there any formula to calculate an average daily balance for a set date range? When downloading BOFA data to excel, it only provides a row for days with activity, so I can't just do a simple average function. Or a quick way to 'fill in' all the missing days with the same balance as the previous day?
That would be simpler. Done so many times. 

Not by a computer now, but IIRC the logic is "If cell is empty, use cell above it, otherwise use cell". Try in a new column next to the column with missing data.

Will check back once by a computer with an exact formula.

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3855
  • Total likes: 70
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #297 on: May 17, 2015, 08:42:37 PM »
1. Select the range that contains blank cells you need to fill.
2. Click Home > Find & Select > Go To Special…, and a Go To Special dialog box will appear, then check Blanks option.

3.Click OK, and all of the blank cells have been selected. Then input the formula “=A2” into active cell A3 without changing the selection. This cell reference can be changed as you need.

4. Press Ctrl + Enter, Excel will copy the respective formula to all blank cells.

5. At this point, the filled contents are formulas, and we need to convert the formals to values. Then select the whole range, right-click to choose Copy, and then press Ctrl + Alt + V to active the Paste Special… dialog box. And select Values option from Paste, and select None option from Operation.

6.Then click OK. And all of the formulas have been converted to values.

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2012
  • Posts: 775
  • Total likes: 45
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #298 on: June 02, 2015, 03:54:13 PM »
I have a set of data and would like any tips of the best way to present it so one can see the trends easily.
Column A - Month
Column B - Payee
Column C - Service Provided (there are only 3 choices)
Column D - number of items
Column E - Total cost of items

I would prefer some sort of chart.

Any help is greatly appreciated.
TIA
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5386
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #299 on: June 02, 2015, 05:22:32 PM »
I have a set of data and would like any tips of the best way to present it so one can see the trends easily.
Column A - Month
Column B - Payee
Column C - Service Provided (there are only 3 choices)
Column D - number of items
Column E - Total cost of items

I would prefer some sort of chart.

Any help is greatly appreciated.
TIA
Excel has a great Pivot Chart feature built in. Should be exactly what you're looking for.