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

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17285
  • Total likes: 14082
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1540 on: October 20, 2022, 10:23:20 AM »
What is the hardcoded number you're using for row? Why can't that be replaced with a formula?

Also, switching from HLOOKUP to INDEX, even with MATCH for both column and row, would not slow it down. If anything it would speed it up. Concatenate is a non-issue (you could also just do "month&year").

I think I need to explain the question better.
Feelings don't care about your facts

Offline WAM

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: May 2010
  • Posts: 1389
  • Total likes: 307
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: Location, location, location.
Re: Excel Help and Problems
« Reply #1541 on: October 23, 2022, 03:31:12 AM »
I think I need to explain the question better.

I don't know if understand your original question, but would the ROW formula help you at all?

Not sure if the following would help either, but when I have no patience to figure out how to deal with dragging down not ascending a formula as I'd like, I break down the formula into separate cells (with a leading ' for the parts I want to remain the same in) and then drag down the numbers I want to ascend. Finally, I put it all back together using CONCAT and then c/p to wherever I want the formulas to be.
Hopefully that made some sense.

Offline Yo ssi

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Aug 2019
  • Posts: 6558
  • Total likes: 2511
  • DansDeals.com Hat Tips 59
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1542 on: October 25, 2022, 08:49:30 AM »
Sometimes switching to Google sheets temporarily helps
_    ,
' )  /
 /  / __   _   _   o
(__/_(_)  /_)_/_)_<_
 //
(/

Offline avadah

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2012
  • Posts: 2500
  • Total likes: 38
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Jerusalem
Re: Excel Help and Problems
« Reply #1543 on: December 29, 2022, 09:32:38 PM »
I'm creating a template and I'm looking for a way to pull the unique values from a range but I don't know what the range is bec it will change every time. I know I don't want it to start before B50 but I don't know how far down it will go. Is there a way to select a whole column as a range minus the first 50 cells?

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 2162
  • Total likes: 365
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1544 on: December 29, 2022, 09:45:39 PM »
I'm creating a template and I'm looking for a way to pull the unique values from a range but I don't know what the range is bec it will change every time. I know I don't want it to start before B50 but I don't know how far down it will go. Is there a way to select a whole column as a range minus the first 50 cells?
Why not just do B50:B10000000?

Or you can do something like:
Code: [Select]
INDEX(B:B,50):INDEX(B:B,ROWS(B:B))

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12825
  • Total likes: 3272
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1545 on: December 29, 2022, 09:52:28 PM »
this is likely not helpful but in google sheets you can do B51:B to get the populated cells in column B starting from row 51
Workflowy. You won't know what you're missing until you try it.

Offline avadah

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2012
  • Posts: 2500
  • Total likes: 38
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Jerusalem
Re: Excel Help and Problems
« Reply #1546 on: December 29, 2022, 10:30:37 PM »
this is likely not helpful but in google sheets you can do B51:B to get the populated cells in column B starting from row 51
I was wondering if excel has something like this.
Why not just do B50:B10000000?
Wouldn't that slow down the computer bec it's so much info?
Or you can do something like:
Code: [Select]
INDEX(B:B,50):INDEX(B:B,ROWS(B:B))
I don't understand this.
Also is there a way to pull unique values if my version of excel doesn't have the =unique function?

Offline Yo ssi

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Aug 2019
  • Posts: 6558
  • Total likes: 2511
  • DansDeals.com Hat Tips 59
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1547 on: December 29, 2022, 10:59:15 PM »
I'm creating a template and I'm looking for a way to pull the unique values from a range but I don't know what the range is bec it will change every time. I know I don't want it to start before B50 but I don't know how far down it will go. Is there a way to select a whole column as a range minus the first 50 cells?

Quote
You can use either OFFSET, either INDIRECT to refer the custom range:
Code: [Select]
OFFSET(A5,0,0,ROWS(A:A)-ROW(A5)+1)
INDIRECT("A5:A"&ROWS(A:A))
Both result in a range from A5 to the bottom of the column.
Reference
_    ,
' )  /
 /  / __   _   _   o
(__/_(_)  /_)_/_)_<_
 //
(/

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12825
  • Total likes: 3272
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1548 on: December 29, 2022, 11:04:05 PM »
I wonder if setting up a Named Range would help? Then you define it once and can use it anywhere in the spreadsheet
Workflowy. You won't know what you're missing until you try it.

Offline avadah

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2012
  • Posts: 2500
  • Total likes: 38
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Jerusalem
Re: Excel Help and Problems
« Reply #1549 on: December 29, 2022, 11:11:43 PM »
Reference
Thanks. Will try it out.

How do I do the following. I want a cell to give me the total amount of a specific color. For example a cell should tell me that there's 33 reds.



Offline Yo ssi

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Aug 2019
  • Posts: 6558
  • Total likes: 2511
  • DansDeals.com Hat Tips 59
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1550 on: December 29, 2022, 11:21:37 PM »
Thanks. Will try it out.

How do I do the following. I want a cell to give me the total amount of a specific color. For example a cell should tell me that there's 33 reds.


SUMIF
_    ,
' )  /
 /  / __   _   _   o
(__/_(_)  /_)_/_)_<_
 //
(/

Offline avadah

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2012
  • Posts: 2500
  • Total likes: 38
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Jerusalem
Re: Excel Help and Problems
« Reply #1551 on: February 01, 2023, 10:39:22 PM »
What formula should I use:
if cell j2 is within 3 months of today then return a2 if not blank.
I know i need an if statement, I just don't know how to make the logic test within 3 months of today
Thanks

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3761
  • Total likes: 69
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1552 on: February 01, 2023, 10:42:45 PM »
What formula should I use:
if cell j2 is within 3 months of today then return a2 if not blank.
I know i need an if statement, I just don't know how to make the logic test within 3 months of today
Thanks
Elsewhere in the sheet (say Z1) put =today()
Then =if(J2+90>Z1,A2,"")

Offline JuryDuty

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2018
  • Posts: 1098
  • Total likes: 220
  • DansDeals.com Hat Tips 18
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1553 on: February 01, 2023, 10:49:07 PM »
I asked chatgpt that question and this is the response:

=AND(A1 >= TODAY() - 90, A1 < TODAY() + 90)
Where A1 is the cell containing the date you want to check. The AND function returns TRUE if both conditions are met (i.e., the date is greater than or equal to today minus 90 days, and less than today plus 90 days).
Jury Duty: Nothing to do there but chill and write a TR. Kinda like the beach but missing the view and ocean breeze.

Offline avadah

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2012
  • Posts: 2500
  • Total likes: 38
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Jerusalem
Re: Excel Help and Problems
« Reply #1554 on: February 01, 2023, 11:04:33 PM »
I asked chatgpt that question and this is the response:

=AND(A1 >= TODAY() - 90, A1 < TODAY() + 90)
Where A1 is the cell containing the date you want to check. The AND function returns TRUE if both conditions are met (i.e., the date is greater than or equal to today minus 90 days, and less than today plus 90 days).
So let's say i want it to return a certain cell if within 3 months, not true or false

Offline JuryDuty

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2018
  • Posts: 1098
  • Total likes: 220
  • DansDeals.com Hat Tips 18
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1555 on: February 01, 2023, 11:16:38 PM »
Elsewhere in the sheet (say Z1) put =today()
Then =if(J2+90>Z1,A2,"")

He said it best (although you can ask chatgpt). Main difference I had was the AND for before or after, but you can incorporate that into his just as well
Jury Duty: Nothing to do there but chill and write a TR. Kinda like the beach but missing the view and ocean breeze.

Offline Definitions

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jan 2018
  • Posts: 1748
  • Total likes: 479
  • DansDeals.com Hat Tips 3
    • View Profile
Re: Excel Help and Problems
« Reply #1556 on: February 01, 2023, 11:53:01 PM »
So let's say i want it to return a certain cell if within 3 months, not true or false
=if(AND(J2 >= TODAY() - 90, J2 <= TODAY() + 90,isblank(A2)=false),A2,"")

I think that should work. Note this is a range of 90 days for before and after. I get confused if the 90th day is included.

Also on searching it seems like the isblank function returns false even if the cell contains a formula. Keep that in mind
« Last Edit: February 02, 2023, 12:03:30 AM by Definitions »
My Tapatalk notifications don't always work.

Offline avadah

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2012
  • Posts: 2500
  • Total likes: 38
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Jerusalem
Re: Excel Help and Problems
« Reply #1557 on: February 02, 2023, 11:20:13 PM »
Thanks

Offline CountValentine

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Mar 2013
  • Posts: 15742
  • Total likes: 7272
  • DansDeals.com Hat Tips -1
  • Gender: Female
    • View Profile
  • Location: Poland - Exiled
  • Programs: DAOTYA, DDF Level 3, 5K Lounge
Re: Excel Help and Problems
« Reply #1558 on: February 05, 2023, 01:26:57 PM »
This is the formula I am using in one of the cells. If it returns a positive number I would like it to be in green and a negative number to be in red. Can it be done?

=SUM(M2:M7)-SUM(M8:M10)-M16
Only on DDF does 24/6 mean 24/5/half/half

Offline Yo ssi

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Aug 2019
  • Posts: 6558
  • Total likes: 2511
  • DansDeals.com Hat Tips 59
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1559 on: February 05, 2023, 01:28:33 PM »
This is the formula I am using in one of the cells. If it returns a positive number I would like it to be in green and a negative number to be in red. Can it be done?

=SUM(M2:M7)-SUM(M8:M10)-M16
Conditional formatting on the cell
_    ,
' )  /
 /  / __   _   _   o
(__/_(_)  /_)_/_)_<_
 //
(/