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 287157 times)

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Help and Problems
« Reply #1160 on: April 25, 2018, 04:21:58 PM »
Hey there folks, would greatly appreciate if anyone can help an Excel NooB with this:

I have a formula:
=J2*D2
But, I want to exclude any row where J = 0.

So I want J*D UNLESS J=0, in which case I want the value of D.
This should do it:
Code: [Select]
=IF(J2=0,D2,J2*D2)

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17395
  • Total likes: 14333
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1161 on: April 25, 2018, 04:22:11 PM »
=if(J2=0,D2,(J2*D2))
Feelings don't care about your facts

Online Yehuda57

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jan 2014
  • Posts: 4891
  • Total likes: 14679
  • DansDeals.com Hat Tips 6
    • View Profile
    • Squilled
  • Location: Brooklyn
  • Programs: Official Dansdeals salad correspondent
Re: Excel Help and Problems
« Reply #1162 on: April 25, 2018, 05:07:09 PM »

Offline Yehuda25

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 6069
  • Total likes: 78
  • DansDeals.com Hat Tips 9
    • View Profile
Re: Excel Help and Problems
« Reply #1163 on: April 25, 2018, 09:09:44 PM »
In google sheets, im trying to have everything in column E multiply column F which will give me my total in column g, However the header names of the column are making the formula not go... because they are names and not values, any workaround?
“To avoid criticism say nothing, do nothing, be nothing.”


― Aristotle

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1164 on: April 25, 2018, 09:10:58 PM »
In google sheets, im trying to have everything in column E multiply column F which will give me my total in column g, However the header names of the column are making the formula not go... because they are names and not values, any workaround?
Do you need to put the formula in the header row also? Why not start in G2?
Workflowy. You won't know what you're missing until you try it.

Offline Yehuda25

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 6069
  • Total likes: 78
  • DansDeals.com Hat Tips 9
    • View Profile
Re: Excel Help and Problems
« Reply #1165 on: April 25, 2018, 09:13:32 PM »
Do you need to put the formula in the header row also? Why not start in G2?
I can, but how do I have it go to the rest of the sheet automatically?
“To avoid criticism say nothing, do nothing, be nothing.”


― Aristotle

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1166 on: April 25, 2018, 09:16:46 PM »
I can, but how do I have it go to the rest of the sheet automatically?
You can copy it down to the end by selecting the cell with the formula, control-c, control-shift-downarrow, control-v. If you ever add more rows at the bottom you'll have to copy the formulas again.
Workflowy. You won't know what you're missing until you try it.

Offline Yehuda25

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 6069
  • Total likes: 78
  • DansDeals.com Hat Tips 9
    • View Profile
Re: Excel Help and Problems
« Reply #1167 on: April 25, 2018, 09:25:06 PM »
You can copy it down to the end by selecting the cell with the formula, control-c, control-shift-downarrow, control-v. If you ever add more rows at the bottom you'll have to copy the formulas again.
thanks!
“To avoid criticism say nothing, do nothing, be nothing.”


― Aristotle

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1168 on: April 25, 2018, 09:36:49 PM »
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: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Help and Problems
« Reply #1169 on: April 25, 2018, 11:36:08 PM »
In google sheets, im trying to have everything in column E multiply column F which will give me my total in column g, However the header names of the column are making the formula not go... because they are names and not values, any workaround?
You can copy it down to the end by selecting the cell with the formula, control-c, control-shift-downarrow, control-v. If you ever add more rows at the bottom you'll have to copy the formulas again.
There's a cleaner way to do it with Array Formulas, so you don't need to keep extending it.

https://www.benlcollins.com/formula-examples/array-formulas-forms/

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1170 on: April 25, 2018, 11:39:08 PM »
There's a cleaner way to do it with Array Formulas, so you don't need to keep extending it.

https://www.benlcollins.com/formula-examples/array-formulas-forms/
Cool!

Workflowy. You won't know what you're missing until you try it.

Offline Yehuda25

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 6069
  • Total likes: 78
  • DansDeals.com Hat Tips 9
    • View Profile
Re: Excel Help and Problems
« Reply #1171 on: April 26, 2018, 01:56:10 AM »
There's a cleaner way to do it with Array Formulas, so you don't need to keep extending it.

https://www.benlcollins.com/formula-examples/array-formulas-forms/
interesting thanks.
“To avoid criticism say nothing, do nothing, be nothing.”


― Aristotle

Offline Something Fishy

  • Global Moderator
  • Dansdeals Lifetime Presidential Platinum Elite
  • **********
  • Join Date: Jan 2011
  • Posts: 8707
  • Total likes: 6069
  • DansDeals.com Hat Tips 44
    • View Profile
    • Kosher Horizons
  • Location: Iceland
Re: Excel Help and Problems
« Reply #1172 on: May 15, 2018, 09:00:41 AM »
Is there a way to sort numbers correctly if the field isn't just numbers? For instance, I want the following to be sorted largest to smallest:

100s
5000+
34

Bonus points if they can still be used in calculations - I'm mainly looking to total them. Of course it won't be exact, but 5164 would be acceptable.
Check out my site for epic kosher adventures: Kosher Horizons

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1173 on: May 15, 2018, 09:07:25 AM »
Is there a way to sort numbers correctly if the field isn't just numbers? For instance, I want the following to be sorted largest to smallest:

100s
5000+
34

Bonus points if they can still be used in calculations - I'm mainly looking to total them. Of course it won't be exact, but 5164 would be acceptable.
You can probably use regexextract to extract just the numerical digits, in a formula in a separate column. Then sort by and total the second column.

You can do regexextract in google sheets or with an excel addon
Workflowy. You won't know what you're missing until you try it.

Offline Something Fishy

  • Global Moderator
  • Dansdeals Lifetime Presidential Platinum Elite
  • **********
  • Join Date: Jan 2011
  • Posts: 8707
  • Total likes: 6069
  • DansDeals.com Hat Tips 44
    • View Profile
    • Kosher Horizons
  • Location: Iceland
Re: Excel Help and Problems
« Reply #1174 on: May 15, 2018, 09:14:57 AM »
You can probably use regexextract to extract just the numerical digits, in a formula in a separate column. Then sort by and total the second column.

You can do regexextract in google sheets or with an excel addon

Thanks, but an extra column with essentially the same data won't work in this sheet.

Any way to do it in just one column?
Check out my site for epic kosher adventures: Kosher Horizons

Offline Something Fishy

  • Global Moderator
  • Dansdeals Lifetime Presidential Platinum Elite
  • **********
  • Join Date: Jan 2011
  • Posts: 8707
  • Total likes: 6069
  • DansDeals.com Hat Tips 44
    • View Profile
    • Kosher Horizons
  • Location: Iceland
Re: Excel Help and Problems
« Reply #1175 on: May 15, 2018, 09:27:17 AM »
Found the solution here:

https://www.extendoffice.com/documents/excel/4264-excel-display-different-text-than-value.html

Basically I'm inputting 5000 but the cell is displaying 5000+. Slightly convoluted, but it works.

Now let's see if I can get it to work in Google Docs as well :P.
Check out my site for epic kosher adventures: Kosher Horizons

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1176 on: May 15, 2018, 10:03:44 AM »
Found the solution here:

https://www.extendoffice.com/documents/excel/4264-excel-display-different-text-than-value.html

Basically I'm inputting 5000 but the cell is displaying 5000+. Slightly convoluted, but it works.

Now let's see if I can get it to work in Google Docs as well :P.
So you want them all to display a + after the number?
Workflowy. You won't know what you're missing until you try it.

Offline Something Fishy

  • Global Moderator
  • Dansdeals Lifetime Presidential Platinum Elite
  • **********
  • Join Date: Jan 2011
  • Posts: 8707
  • Total likes: 6069
  • DansDeals.com Hat Tips 44
    • View Profile
    • Kosher Horizons
  • Location: Iceland
Re: Excel Help and Problems
« Reply #1177 on: May 15, 2018, 10:05:26 AM »
So you want them all to display a + after the number?

Not all, only a few. Few enough that this process won't be that much of a pain.
Check out my site for epic kosher adventures: Kosher Horizons

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1178 on: May 15, 2018, 10:07:37 AM »
Not all, only a few. Few enough that this process won't be that much of a pain.
So how does Excel know which ones to add it to? Are you only applying the conditional formatting to those cells?
Workflowy. You won't know what you're missing until you try it.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1179 on: May 15, 2018, 10:14:07 AM »
You can do it in google sheets by applying a number format. Select the cell(s) you want, click Format > Number > More Formats > Custom Number Format. Enter this for the format:

Code: [Select]
#,#+
(won't work if you have decimals, we'll have to tweak it then)
Workflowy. You won't know what you're missing until you try it.