Conditional Calculations Based On Drop Downs

[dosman]

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.

 

[etaf]

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 ?

 

[dosman]

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]

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]

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

 

[dosman]

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]

you are welcome