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

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12988
  • Total likes: 3454
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #400 on: October 30, 2015, 10:50:00 AM »
BTW, someone at my company came up with a really cool trick to make use of the multiple instance concept. Basically, you can't normally make use of all your CPU power (ie multiple cores) from a macro. So let's say you have a macro that's supposed to loop through a bunch of 'cases' and create output for each set of 'case' inputs. Instead of just letting it loop, the macro opens multiple instances of Excel and assigns 'cases' to each one for processing, then stores all the results in the sames spot. If you have 4 cores, you can finish the run of 'cases' close to 400% faster.
COOL!

I'd love to see sample code, if you have.
Workflowy. You won't know what you're missing until you try it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #401 on: October 30, 2015, 11:15:57 AM »
COOL!

I'd love to see sample code, if you have.
Sorry, it's considered proprietary. But others have done it online, if you spend some time googling it you'll probably find an example.

Offline mancunian

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2008
  • Posts: 1303
  • Total likes: 18
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #402 on: October 30, 2015, 11:18:54 AM »
That is very cool. BTW why in the world do computers not make use of the processors? It makes no sense to me at all that work is not allocated properly and we have to buy better computers.

I just kept my old computer on my desk and when my computer gets stuck I turn and work there

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12988
  • Total likes: 3454
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #403 on: October 30, 2015, 12:30:09 PM »
Sorry, it's considered proprietary. But others have done it online, if you spend some time googling it you'll probably find an example.
ok, one day when I actually need it :)

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

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #404 on: October 30, 2015, 12:39:11 PM »
That is very cool. BTW why in the world do computers not make use of the processors? It makes no sense to me at all that work is not allocated properly and we have to buy better computers.

I just kept my old computer on my desk and when my computer gets stuck I turn and work there
It's generally a software issue. If the software is written properly, it'll make use of all available resources. In the case of Excel, you can choose how many cores to make use of, but that only affects the workbook calcs, not macros. The basic reason for this is that Excel functions are defined and known to MS developers when they built the program, so they can optimize how tasks get spread over multiple cores. But there's no real way to make a general method for spreading VBA/Macro tasks over multiple cores, as the possibilities are unlimited.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12988
  • Total likes: 3454
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #405 on: November 11, 2015, 01:09:17 PM »
Is there a way to have a function display whether or not a certain cell has a background color?
Workflowy. You won't know what you're missing until you try it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #406 on: November 11, 2015, 01:45:02 PM »
Is there a way to have a function display whether or not a certain cell has a background color?
Nope. You need a UDF. Here's one:

Function FillColor(Target As Range) As Variant
    If Target.Interior.ColorIndex = -4142 Then
        FillColor = True
    Else
        FillColor = False
    End If
End Function

It has issues with refreshing when the target cell changes, but I'm sure if you google it a bit you can fix that too.

Then you'd use the formula =FillColor(A1), for example, to check whether A1 has a background color or not. -4142 is the ColorIndex for no color.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12988
  • Total likes: 3454
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #407 on: November 11, 2015, 06:34:31 PM »
Nope. You need a UDF. Here's one:

Function FillColor(Target As Range) As Variant
    If Target.Interior.ColorIndex = -4142 Then
        FillColor = True
    Else
        FillColor = False
    End If
End Function

It has issues with refreshing when the target cell changes, but I'm sure if you google it a bit you can fix that too.

Then you'd use the formula =FillColor(A1), for example, to check whether A1 has a background color or not. -4142 is the ColorIndex for no color.
cool, thanks!
Workflowy. You won't know what you're missing until you try it.

Offline joshnuss

  • Dansdeals Gold Elite
  • ***
  • Join Date: Apr 2014
  • Posts: 188
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #408 on: November 12, 2015, 10:18:07 AM »
Question to all the Excel Experts. I am making a spreadsheet with a pricing structure for my company. The price depends on 2 factors quantity(3 levels of quantities) and price(2 levels). All together there are 6 different options I.E. If the price is less than 1 dollar and the quantity is 500 than the markup is this.
I used a IF(AND Function 6 times. Is there a better way?
This was Mine:
=IF(AND(A2>=500,B2<=1),B2*1.05,IF(AND(A2>=300,B2<=1),B2*1.10,IF(AND(A2>=100,B2<=1),B2*1.15,IF(AND(A2>=500,B2>1),B2*1.04,IF(AND(A2>=300,B2>1),B2*1.08,IF(AND(A2>=100,B2>1),B2*1.13," "))))))


Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #409 on: November 12, 2015, 10:26:12 AM »
Question to all the Excel Experts. I am making a spreadsheet with a pricing structure for my company. The price depends on 2 factors quantity(3 levels of quantities) and price(2 levels). All together there are 6 different options I.E. If the price is less than 1 dollar and the quantity is 500 than the markup is this.
I used a IF(AND Function 6 times. Is there a better way?
This was Mine:
=IF(AND(A2>=500,B2<=1),B2*1.05,IF(AND(A2>=300,B2<=1),B2*1.10,IF(AND(A2>=100,B2<=1),B2*1.15,IF(AND(A2>=500,B2>1),B2*1.04,IF(AND(A2>=300,B2>1),B2*1.08,IF(AND(A2>=100,B2>1),B2*1.13," "))))))
Your pricing is not quite formulaic (if that 1.13 would've been 1.12 you'd be in better shape), so the only other way would be a table, and then a formula to read from the table.

Offline mancunian

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2008
  • Posts: 1303
  • Total likes: 18
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #410 on: November 12, 2015, 10:29:35 AM »
What he's saying is to think about making it formulaic.

I did the same thing a long time ago. What I tried to do was separate the two factors in a way where they can be combined...
So you get like a calculation that works across the board for all your options. Like for the first part (price below 1) you could do for every hundred pieces less stock raise the price 0.02

That sort of thing

Offline joshnuss

  • Dansdeals Gold Elite
  • ***
  • Join Date: Apr 2014
  • Posts: 188
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #411 on: November 12, 2015, 10:30:53 AM »
That was an example. The real pricing is based on that the company makes the same amount of money Gross profit regardless of the quantity. Meaning if for 500 units the markup is 10% than for 250 units the markup would be 20%. (just an example)

Offline mancunian

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2008
  • Posts: 1303
  • Total likes: 18
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #412 on: November 12, 2015, 10:32:44 AM »
Whatever. But you get the point. You take the real factors (like target gross margin) and build that in instead of options

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #413 on: November 12, 2015, 10:33:52 AM »
That was an example. The real pricing is based on that the company makes the same amount of money Gross profit regardless of the quantity. Meaning if for 500 units the markup is 10% than for 250 units the markup would be 20%. (just an example)
Wouldn't it be easier to add the profit as a dollar amount then? Either way, if it is in fact formulaic, I can't turn it into an Excel formula without a proper description of the pricing formula.

Offline joshnuss

  • Dansdeals Gold Elite
  • ***
  • Join Date: Apr 2014
  • Posts: 188
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #414 on: November 12, 2015, 10:45:06 AM »
Thanks guys. I guess than it is more of a math question. How to figure out that regardless of quantity the profit margin is still the same?

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #415 on: November 12, 2015, 11:25:42 AM »
Thanks guys. I guess than it is more of a math question. How to figure out that regardless of quantity the profit margin is still the same?
You have to define your terms. Are you looking for a certain dollar amount per sale, regardless of quantity? Are you looking for a certain percentage profit (over actual cost) on each item sold?

Offline joshnuss

  • Dansdeals Gold Elite
  • ***
  • Join Date: Apr 2014
  • Posts: 188
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #416 on: November 12, 2015, 11:36:21 AM »
You have to define your terms. Are you looking for a certain dollar amount per sale, regardless of quantity? Are you looking for a certain percentage profit (over actual cost) on each item sold?
A certain Dollar amount regardless of quantity. The point is that it would really hurt to order less quantity.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #417 on: November 12, 2015, 11:53:29 AM »
A certain Dollar amount regardless of quantity. The point is that it would really hurt to order less quantity.
Okay. So shouldn't your formula just be [quantity * unit price + fixed profit margin]?

Offline joshnuss

  • Dansdeals Gold Elite
  • ***
  • Join Date: Apr 2014
  • Posts: 188
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #418 on: November 12, 2015, 12:00:25 PM »
Okay. So shouldn't your formula just be [quantity * unit price + fixed profit margin]?
How would that work?? The number changes depending on the price of the highest tier quantity wise. Let's say the price for 1000 units is 5 dollars than 500 and 200 units would be priced accordingly. But if the Price for 1000 units is 3 dollars than we would need to match that profit margin for 500 and 200 units too. It would have to be a formula based on the highest tier price that's how you should charge the lower amount quantity wise.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4079
  • Total likes: 837
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #419 on: November 12, 2015, 12:05:21 PM »
How would that work?? The number changes depending on the price of the highest tier quantity wise. Let's say the price for 1000 units is 5 dollars than 500 and 200 units would be priced accordingly. But if the Price for 1000 units is 3 dollars than we would need to match that profit margin for 500 and 200 units too. It would have to be a formula based on the highest tier price that's how you should charge the lower amount quantity wise.
You're really not explaining yourself well at all. What are the 2 tiers? Are those related to the actual cost of the items, to the profit margin, both?

In your numbers above, is $5 the cost for 1000 units, or is it $5 per unit?