• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Help With ColorMath Function|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Help With ColorMath Function|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Help With ColorMath Function
sp_PrintTopic sp_TopicIcon
Help With ColorMath Function
Avatar
David Simon

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
April 12, 2019
sp_UserOfflineSmall Offline
1
April 12, 2019 - 4:32 am
sp_Permalink sp_Print

Okay...I am such a super noob when it comes to excel.  So please flame me if you must oh epic Excel Gods but have mercy on this pitiful fool for not having the necessary brain power to work through this on my own.

Here is what I am doing.   

 

I can't yet add images or I would.  If the admin will allow I would be happy to upload a word document with the snapshot of what I am doing.  Since it is proprietary info from my company I don't want to attache the excel because I am trying my best to respect my company NDA but also get help to improve an internal process asset that is not working as good as it could.  

SO:

I have four columns set up (Supplier A)

1. Material Supplied - Under this column I give the total cost of the good they are supplying.

2.  Labor Supplied - This column has the labor cost to install the material supplied.

3. Sub-Total - This column has Columns 1&2 added.

4. Substitution - This column has a drop down selection box with a YES or NO answer. 

 

Then I have that same four column set up repeated only difference is this is a different supplier.  So again:

Four Columns Set up (Supplier B)

1. Material Supplied - Under this column I give the total cost of the good they are supplying.

2.  Labor Supplied - This column has the labor cost to install the material supplied.

3. Sub-Total - This column has Columns 1&2 added.

4. Substitution - This column has a drop down selection box with a YES or NO answer. 

 

Then the NEXT COLUMN is SUB-BUDGET Column which I want to do the following and I have figured it out except one key element. 

Currently I am running the MIN(Column 3, Column 7) and it returns the lowest price cost for Labor + Material.

 

However SOMETIMES there are substitutions on the materials, meaning they are not the specs called for but a substitute which we MAY or MAY NOT choose to use.  To show this I have set up conditional formating, so when the Substitution column shows a NO answer the cell remains as is, but when it shows a YES answer it is filled with the color black, and the font is changed to white to allow us to easily see what is a substitute.  

 

MY ASK:

 

I want to do a MIN(Column 3, Column 7) BUT EXCLUDE the substitutes almost as if they were a 0 sum exclusion.  Meaning I do not want to have the MIN applied to any column 3 or column 7 item which is filled black, with white font because it is a substitute and it can not be auto filled into the Sub-Budget column until a submittal request is made to the Designer/Architect who can approve and we can change this at a later date.  

The end result is to get the Sub-Budget column (Column 9) to fill with the Minimum value between Column 3 & 7 UNLESS the Column is filled with black and font is white.  

Can anyone help me? 

 

IF so you will have to go very in detail, step by step because I know almost nothing about the back end, macro, creating new functions side of Excel.  So an insane amount of patience and detail will be necessary to lead me through this.  

 

Thank you to whoever endeavors to help me here!

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1513
Member Since:
October 5, 2010
sp_UserOnlineSmall Online
2
April 12, 2019 - 8:47 am
sp_Permalink sp_Print

Hi David,

There's nothing to prevent you uploading an image or Word doc to this post, it would be better to provide an Excel workbook though.

Trying to understand your data layout and then recreating it can take time and lead to mistakes when we are trying to work out a solution.  Even just some sample data you make up is better than nothing.  Try to give us an accurate representation of how your data is laid out, and the types of data we are dealing with.

With regards the solution, it may not require VBA.  Conditional formatting may work, or something else.  At this stage I'm not sure as I don't fully understand the problem yet.

Regards

Phil

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
3
April 13, 2019 - 4:41 am
sp_Permalink sp_Print

Hello,

Not sure if the example in the attached file is good enough, but it is at least simple and do show what you want to see.

Br,
Anders

Avatar
David Simon

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
April 12, 2019
sp_UserOfflineSmall Offline
4
April 19, 2019 - 1:14 am
sp_Permalink sp_Print

Ok I have attached the file and this should make it easier.

 

Columns T & U are the Materials & Labor for Fergusson Appliances.

Column V is the Sub-Total of these Columns. 

 

Columns X & Y are the Materials & Labor for Earth Elements Appliances.

Column Z is the Sub-Total of these Columns. 

 

Columns W & AA are the SUBSTITUTION Columns.  This is important to understand why this is here.  When a client wants a Gaggenau refrigerator or Electric Grill or Cooktop, they want what they want they don't want something almost as good.  However not all appliance suppliers can supply a Gaggenau.  Sometimes they can supply a close SUBSTITUTE.  I like to show that as an option but it should NOT be calculated in the budget because it is a substitute.  

Now then how this works is if the item is not a substitute then I have conditional formatting paired with a MIN function that auto-highlights in GREEN the lowest price Sub-Total price of columns V & Z.  

The problem is that I can't use MIN on the substitutes.  Often times the Substitutes will be the least expensive option but they are not really an option because they are not what the client wants.  They are substitutes.  

So then what I want to do is to have the MIN function IGNORE anything where the Substitute column is marked with a YES.  

 

This will auto fill the cell color in green which is not a substitute and is also the lowest price.  So this way if I have three suppliers, and one has a substitute and it is the lowest price, it is not included, so the other two are the suppliers which are then calculated to know which one is BOTH 1) what the client wants exactly, and, 2) the least expensive option and is then cell filled with green.

 

Ultimately there is more I would like to do to this worksheet as well.

In the AR & AS & AT & AU columns I would like the lowest priced items that are not substitutes to be repeated here and auto populated but that is a whole other project for a whole other day.  

 

So you know I spent hours and hours and hours trying to work through this problem.  Somewhere out there is someone who can do this in 4 minutes or less and its killing me.

Avatar
David Simon

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
April 12, 2019
sp_UserOfflineSmall Offline
5
April 19, 2019 - 1:18 am
sp_Permalink sp_Print sp_EditHistory

Philip Treacy said
Hi David,

There's nothing to prevent you uploading an image or Word doc to this post, it would be better to provide an Excel workbook though.

Sorry I must not have seen that option earlier!  I have attached now so you can see what I am working on!

Avatar
David Simon

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
April 12, 2019
sp_UserOfflineSmall Offline
6
April 19, 2019 - 1:21 am
sp_Permalink sp_Print sp_EditHistory

Not sure if I attached the file or not...Oh there it is! Ok it is the same file I just uploaded twice I guess.  Sorry!

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
7
April 20, 2019 - 7:24 am
sp_Permalink sp_Print

Hello,

A simple solution is to use a formula with nested IF's. Check out the attached file and see if it is what you want.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Philip Treacy, Carlos Ferreira, Chandler Davis, baber Tufail
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
drsven
Annie Witbrod
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27211

 

Member Stats:
Guest Posters: 49
Members: 31880
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.