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

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12833
  • Total likes: 3132
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1560 on: February 05, 2023, 01:28:46 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
Sure, with conditional formatting. You'll want to set up 2 rules, one for Greater Than 0 and one for Less Than 0.
Workflowy. You won't know what you're missing until you try it.

Offline yoruel

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: May 2013
  • Posts: 755
  • Total likes: 9
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1561 on: February 22, 2023, 04:41:24 PM »
Excel Formula for Cell Comparison. Looking to see % of incorrectness.

Offline Definitions

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jan 2018
  • Posts: 1751
  • Total likes: 477
  • DansDeals.com Hat Tips 3
    • View Profile
Re: Excel Help and Problems
« Reply #1562 on: February 22, 2023, 11:32:05 PM »
Excel Formula for Cell Comparison. Looking to see % of incorrectness.
assuming you mean this https://www.calculatorsoup.com/calculators/algebra/percent-change-calculator.php

Cell A1 is number 1
Cell B1 is number 2
Cell C1 is the difference of number 1 to number 2

Then do in cell C1:
=((B1-A1)/A1)*100

(Percentages always confuse me. if someone can explain the two types of calculators in the link above (percentage change and percentage difference) it would be greatly appreciated)
My Tapatalk notifications don't always work.

Offline yoruel

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: May 2013
  • Posts: 755
  • Total likes: 9
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1563 on: February 23, 2023, 06:21:03 AM »
I have in A1 text 25 in B1 I have text 26. =A1=A2 will be false.
What % of A1 & A2 are the same?

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3783
  • Total likes: 808
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1564 on: February 23, 2023, 09:39:05 AM »
I have in A1 text 25 in B1 I have text 26. =A1=A2 will be false.
What % of A1 & A2 are the same?
This is extremely difficult to do in Excel. Possibly doable in VBA, but it wouldn't be simple. This is a similar, though you'd need to know how to use their code to set up a macro to get it to work:

https://www.extendoffice.com/documents/excel/3716-excel-compare-two-strings-highlight-differences-for-similarity.html

Offline Definitions

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jan 2018
  • Posts: 1751
  • Total likes: 477
  • DansDeals.com Hat Tips 3
    • View Profile
Re: Excel Help and Problems
« Reply #1565 on: February 23, 2023, 10:09:02 AM »
I have in A1 text 25 in B1 I have text 26. =A1=A2 will be false.
What % of A1 & A2 are the same?
Meaning you want to see the percentage of characters that are the same?
Is there a limit to how long a string is (if yes how long)? Are the two strings always the same length?
My Tapatalk notifications don't always work.

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5345
  • Total likes: 735
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Help and Problems
« Reply #1566 on: February 23, 2023, 01:17:06 PM »
I have in A1 text 25 in B1 I have text 26. =A1=A2 will be false.
What % of A1 & A2 are the same?
This?

=COUNTIF(C1:C10,"true")/COUNTA(C1:C10)



Offline yoruel

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: May 2013
  • Posts: 755
  • Total likes: 9
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1567 on: February 23, 2023, 01:23:21 PM »
Meaning you want to see the percentage of characters that are the same?
Is there a limit to how long a string is (if yes how long)? Are the two strings always the same length?

No limit. They are jot the same.

I found a macro.

Similarity.  I think I got it

Offline yoruel

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: May 2013
  • Posts: 755
  • Total likes: 9
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1568 on: February 23, 2023, 01:24:37 PM »
This is extremely difficult to do in Excel. Possibly doable in VBA, but it wouldn't be simple. This is a similar, though you'd need to know how to use their code to set up a macro to get it to work:

https://www.extendoffice.com/documents/excel/3716-excel-compare-two-strings-highlight-differences-for-similarity.html

This may work. This is the concept.

Offline unavailable

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Dec 2013
  • Posts: 544
  • Total likes: 18
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Help and Problems
« Reply #1569 on: May 22, 2023, 08:38:47 PM »
Hi
Looking for someone that can help me create an excel sheet that can combine multiple reports into 1.

Let's say I own 4 store locations and they are run in separate QuickBooks files. QB creates the report for each location. How can I combine all into 1 report. It is complicated by the fact that the locations don't have the same exact items(lines), ie a few expenses only apply to some locations. So I can't just do c/p.

Any suggestions,
thank you 

Offline yoruel

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: May 2013
  • Posts: 755
  • Total likes: 9
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1570 on: May 22, 2023, 08:53:24 PM »
Try putting the 4 reports on 1 sheet, ON 4 SEPARATE TABS.
Right click the tab name>copy> make sure you put all onto the same sheet
.

In new tab you can copy headers, then for rest of report you can copy the data a2 = sheet1!a12 ETC

Offline unavailable

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Dec 2013
  • Posts: 544
  • Total likes: 18
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Help and Problems
« Reply #1571 on: May 22, 2023, 09:02:41 PM »
if I understood correctly this would work fine if the reports were the same.
but with different lines on each report, I cant copy the entire data c/p.
Of course I could do cell by cell, but then what good is excel.

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 2149
  • Total likes: 360
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1572 on: May 22, 2023, 11:21:42 PM »
If I understand correctly this would be a human question not an Excel question.

While you might get Excel to pull data based on what you decide should be the same, you obviously cannot tell Excel to "do what's in my head."

So you need to logically map out in your mind what you want the report to look like, and based on that you (or someone can help you) pull the correct data from all the sheets as needed.

Offline Yehudaa

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2018
  • Posts: 3549
  • Total likes: 2311
  • DansDeals.com Hat Tips 111
    • View Profile
Re: Excel Help and Problems
« Reply #1573 on: May 22, 2023, 11:27:17 PM »
Hi
Looking for someone that can help me create an excel sheet that can combine multiple reports into 1.

Let's say I own 4 store locations and they are run in separate QuickBooks files. QB creates the report for each location. How can I combine all into 1 report. It is complicated by the fact that the locations don't have the same exact items(lines), ie a few expenses only apply to some locations. So I can't just do c/p.

Any suggestions,
thank you 
I donít know enough to provide particular advice, but this sounds generally like something that a macro might help with. If nothing else, you may be able to create a macro to add zero-dollar lines for the line items that don't appear on a given sheet, and then either c/p via macro or do that part manually.

No personal experience actually using macros, but Iíve heard theyíre useful for such situations. Maybe look into hiring someone to create the macro and teach you how to run it.

Offline unavailable

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Dec 2013
  • Posts: 544
  • Total likes: 18
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Help and Problems
« Reply #1574 on: May 23, 2023, 11:31:55 AM »
yes, I'm looking for someone that can create/teach me how to.

Willing to pay.

Online farmbochur

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2011
  • Posts: 1790
  • Total likes: 229
  • DansDeals.com Hat Tips 19
    • View Profile
Re: Excel Help and Problems
« Reply #1575 on: May 23, 2023, 03:48:11 PM »


Hi
Looking for someone that can help me create an excel sheet that can combine multiple reports into 1.

Let's say I own 4 store locations and they are run in separate QuickBooks files. QB creates the report for each location. How can I combine all into 1 report. It is complicated by the fact that the locations don't have the same exact items(lines), ie a few expenses only apply to some locations. So I can't just do c/p.

Any suggestions,
thank you

https://youtube.com/shorts/NRMYxNQxN6s?feature=share4
Risk is opportunity

Offline avadah

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2012
  • Posts: 2484
  • Total likes: 34
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Jerusalem
Re: Excel Help and Problems
« Reply #1576 on: July 04, 2023, 05:00:39 PM »
Hi,
I'm trying to figure out a formula that if value x or y or z appears in column a or column b i want the sum of column c minus column d and value x may appear numerous times in the column so i want the total of those sums. (sorry if im not clear)
Thanks

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 16975
  • Total likes: 13530
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1577 on: July 04, 2023, 05:44:30 PM »
Hi,
I'm trying to figure out a formula that if value x or y or z appears in column a or column b i want the sum of column c minus column d and value x may appear numerous times in the column so i want the total of those sums. (sorry if im not clear)
Thanks
Not able to test but I would try this.

=if((or(A1,B1)=or($X$1,$Y$1,$Z$1)),(C1+D1),(what you want to appear if it is not there))

Don't understand the last part
Feelings don't care about your facts

Offline avadah

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2012
  • Posts: 2484
  • Total likes: 34
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Jerusalem
Re: Excel Help and Problems
« Reply #1578 on: July 04, 2023, 10:04:53 PM »
Don't understand the last part
Looking for the sum of all the (c-d) that have an xy or z in the row, not 1 particular row

Offline yoruel

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: May 2013
  • Posts: 755
  • Total likes: 9
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1579 on: July 04, 2023, 10:34:34 PM »
That should be a standard SUMIFS.