Conditional Calculations Based On Drop Downs

dosman

New Member
Joined
Jan 24, 2019
Messages
31
To preface I am trying to decide if formulas, a pivot table, or VB code is the way to tackle my issue. My head is spinning.

I am attempting to calculate earnings based on multiple work based scenarios. I have created drop down validations across multiple columns. Each drop down affects a per unit wage. Most units are based on pages typed while others are based on hours spent on the job. Therefore I need to calculate total revenue based on a formula that can dynamically do math on the various drop down choices and apply each choice's rate of charge to apply to a grand total.

The jobs are currently tracked on a single row with many columns. I need the ability to add as many rows as needed with the formulas/code working as jobs are added. If anyone is willing to help I will gladly share the base file for examination. The following is a loose example.

Cell B3 contains page count I input. Simple numeric. I typed 300 pages. My initial logic was to set up a base cell (lets say B20) to hold a base calculation based on pages multiplied by a rate of $3.75 a page [=sum(b3*3.75)] This is base pay. If I choose a true statement from any of the drop downs (yes/no) I need to manipulate the base number and either add a conditional amount or leave the base calculation alone.

If I choose Yes in B5 that would indicate an additional 50 cents a page. If I choose Yes in B6 that's $1.50 per page added. If I change a response to No the additional fee is removed. I cant store the values for per page rate in the cell being validated to my knowledge. That would be cool if someone knows how. This is a Boolean thing for sure. Just not sure how to tell Excel how to organize it. (If this and that but not this do that) kind of stuff..

Any help is greatly appreciated.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,405
Office Version
  1. 365
Platform
  1. MacOS
you could use an IF () or possible a lookup, lets do a nested if

Will B3 change for a different calculation or does it always hold the number of pages
Most units are based on pages typed while others are based on hours spent on the job
do you have a cell to change from Hours to Pages = "cell for pages" say B21 and has YES NO , if no then hours
B20 would be the pages base rate - so 300

=IF( AND ( B21 = "YES", B5="NO", B6 = "NO" ) , B20*3.75, IF ( AND ( B21="YES", B5="YES", B6 = "NO ), ((B20)*3.75)+((B3-B20)*0.5)), IF(..... etc

you could have B5 show the additional cost as numbers in a drop down , 0.5 and 1.5 , 0 . - that would only need 1 cell and could be used to change the page rate , and simplify the AND () , you would just test to see if pages or hours and then use math on the other cells , as the additional page rate cell with numbers

IF B5 had the rates
=IF( B21 = "YES", ((B20)*3.75)+((B3-B20)*B5)), IF( B21 ="NO", calcs for hours
B20 - 300
((B20)*3.75)+((B3-B20)*B5))
Base rate for the pages in B20 ie 300 , then the difference between what was types in B3 and bse rate X the rate in B5 drop down
With a zero in B5 ie NO additional fee ,then it would just pay the BASE rate of 300 pages *3.75, no matter how many pages are typed

what if you do less than 300 pages.

How do the Hours work ?
 
Last edited:

dosman

New Member
Joined
Jan 24, 2019
Messages
31
Thank you for your insight, friend. i am at work but will give your suggestions a try and clarify hours vs pages this evening. Again Thanks!
 

dosman

New Member
Joined
Jan 24, 2019
Messages
31
After playing around with your suggestions I decided to take your advice and include real numbers in the drop down list. This simplified it greatly. To clarify: the cell containing the page count is always dynamic so the user can input how many pages they typed that day. (court reporter) I am doing this for a friend. I discovered their are many variables in how they are paid based on when the transcript is expected and what accommodations were requested during a deposition. If additional services are requested the per page rate increases and there are a variety of services that can be mixed and matched making the tracking of a total a moving target. Therefore if a service was not requested they choose No from the dropdown the field will not be calculated. Beneath No is the monetary value of the per rate increase (ie $1.00 per page) which will be calculated in the cell doing so.

Again I really appreciate your insight, Wayne.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,405
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

so, have you now solved and require no more help on your question
 

dosman

New Member
Joined
Jan 24, 2019
Messages
31
I am still ironing out a few wrinkles but yes my initial question has been answered. I am still tackling a conditional percentage formula I am trying to simplify. If I need help I may start a ne thread as it really has nothing to do with the topic I started here. Thanks again
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,405
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top