calculating money formula

I would modify the userform

So that whatever values the userform displays, the user can type a value in an adjacent textbox.

The userform would them use that value for its calculation.

Please Login or Register  to view this content.

While we are at it, I always display Data in labels to stop the user overwriting them

So I would use:-

Please Login or Register  to view this content.

On Second thoughts if you are displaying your rates in textboxes and the user modifies the value then that would work in your scenario.

what does "night" mean? this formula returns a specific pay rate based on the contract, the weekday and the start time (if start time greater than 5:00pm it returns the night value)

Formula:

Please Login or Register  to view this content.

does that get you going in the right direction?

and where does that formula go btw

i wrote my formula such that it could go anywhere, but i think it makes most sense to include it in the Agency_Daily_Input tab, starting on row 9. you could also include that in a formula (it returns a $/hr value so you could multiply that whole formula by the # of hours kinda like you're doing in column L)

and i apologize for not being clear, when i said "what does night mean" what i really mean is "what criteria would result in a driver receiving night pay" ... the way i set it up a driver receives night pay if they start after 5:00 PM but if you want all night hours to be charged at one rate and all day hours charged at another that would be a slightly different formula.

[QUOTE=simarui;4042618]what does "night" mean? this formula returns a specific pay rate based on the contract, the weekday and the start time (if start time greater than 5:00pm it returns the night value)

Formula:

Please Login or Register  to view this content.

Originally Posted by simarui

what does "night" mean? this formula returns a specific pay rate based on the contract, the weekday and the start time (if start time greater than 5:00pm it returns the night value)

Formula:

Please Login or Register  to view this content.

does that get you going in the right direction?

does that get you going in the right direction?[/QUOTE]

ok after i had some sleep and i came back to this i had a fresh head on. and realized some mistakes in understanding on both our parts.

i tested this formula and noted it works with the right hand set of figures on the Agency Contract rates but not the left hand side, although the formula covers both sides.

now....i understand where and why you asked about "Nights" which i can see there being a little problem because i need to add a button of sorts to the userform asking if the shift is:

A) Nights or Days (which alleviates the problem of when to start the nights out)
B) if the driver is on overtime after 8 hours (first set of figures on the left) or
C) if the driver is on a flat rate (right hand set of figures)

not sure how to code the userform for that at the moment.

here is my file which changes on userform

https://www.dropbox.com/s/7ts3zs3fkt...Copy.xlsm?dl=0

Please Login or Register  to view this content.

Hi Sir.

id like to know if its possible to change the width of this formula at all.

at the moment it looks up C9:T10, but i noticed that when i put figures only in cols I to N i get an error of #Ref which i cant understand why. so what i did was delete those cols and again i got #REF but i changed the T10 to N10 and it wasnt having any of it. can you help please.

im assuming that all i should be able to change is T10 to N10

Please Login or Register  to view this content.

and it come up with #REF!

your issue is that you're asking the vlookup formula to return a column that is outside of the search range.

vlookup works like this: =vlookup([what am i looking for?],[what is the range i should include in my results? (the first column in this range must be the column in which you are expecting to find your value, and the last column should be the last column from which you expect to return a result)],[which column from that range would you like me to return?],[do you need an exact match? if so, type false, otherwise type true... i almost always default to false here, but if your data is sorted properly you can use true])

All of the stuff in those if() statements is telling excel which column to return. basically the if statements are saying if the date is monday through friday return the 13th column, if it's saturday return the 15th column and if it's sunday return the 17th column. then it's also adding 1 to those numbers if the start time is after 5:00pm.

by reducing the width of your range all those column numbers would need to change to align with the new desired results (i.e. count how many columns away from C you find your new rate)

it is possible to make these #'s more dynamic with index(match()). looks like your dropbox link provided above is no longer working so i'm not exactly sure what to give you there. you can attach a file directly to this thread by clicking go advanced below the reply box and then clicking on the paperclip icon, add file etc...

also i still don't really understand what you mean with A) B) and C) in the earlier description, but i think seeing the file and your new userform would probably make that more clear. it sounds like the last piece of my if() statements in the above formula (which toggles "night" based on start time) should not be included.

Please Login or Register  to view this content.

i tried this and it just comes back #VALUE!

then i did this

Please Login or Register  to view this content.

if yes i get the correct value

if no i get False

Please Login or Register  to view this content.

then i did above code and it works, you vlookup now copied and pasted for later reference. many thanks sir i really mean it

glad you got it working. for reference, unless you need to check that it actually says "No" and not something else or blank or w/e you can actually drop that second embedded if statement. i.e. it seems like this would accomplish your task:

Formula:

Please Login or Register  to view this content.

that formula will return a blank if anything other than "Yes" is found in f9.

as for your first formula that was returning false, did you mean to check D9 in the second if statement? you're checking f9 at first and then d9 later... your first formula would've worked if "No" was found in d9. it returned false because "Yes" was not in F9 and "No" was not in d9 and you didn't give the second if formula a [value if false]. so with your current, "working" formula i suspect you're not actually returning a blank because you found the word "No" ... you're returning a blank because you didn't find the word "No" in d9 (the value after the second comma in that second if statement).

make sense?