I am having trouble creating a formula.

Microsoft Excel 2007
September 2, 2010 at 18:51:43
Specs: Windows XP, 2.4GHZ / 2GB
I have created a spreadsheet to capture payroll for our truck drivers. They are paid by the mile and there are different rates depending on the number of miles in a trip. I have a sheet for each driver and each sheet covers a one week (7 day) period. So there is a possibility of 7 days of miles that fit into one of three categories (>500, <425,>=425&<=500). The other factor that affects the rate of pay is the type of equipment being used (RT or V). What I need to come up with is the total miles for the week under each of 6 possible combinations. Each of the 6 possible answers would appear in their own cell. I could give RT & V a numerical value if necessary. I hope that I have explained it well enough for someone to help.

See More: I am having trouble creating a formula.

Report •


#1
September 2, 2010 at 22:33:15
It might help if you gave us the layout of your sheets.

e.g.

What column(?) are RT and V in.

Do each of the mileage ranges have their own column or are they all in one column and the mileage is in the next column?

etc?

etc?


Report •

#2
September 3, 2010 at 05:29:55
The days of the week from Monday to Sunday are in column A in cells 10 to 16. The miles per day are in column F, cells 10 to 16. The RT or V identifier is in column I, cells 10 to 16. The formula to collect the total miles during the week using V (vans) and with individual trip miles greater than 500 miles is in K22. The formula to collect total miles during the week using V (vans) and with individual trip miles equal to or between 425 and 500 miles is in cell K23. The formula to collect total miles during the week using V (vans) and with individual trip miles less than 425 is in K24. The cells K25, K26 & K27 contain the same formulas as above except for RT (roll tite) equipment. Is this the information that you need (I hope)? If not, please let me know and I will provide whatever else is needed. Tks.

Report •

#3
September 3, 2010 at 07:31:12
Hi,

I created a table of values to go into cells L23 to N27
Each row contained the data for the group, as follows:

	L	M	N
22	V	501	9999
23	V	426	500
24	V	0	425
25	RT	501	9999
26	RT	426	500
27	RT	0	425

Then in cell K22 I entered this formula:=SUMIFS($F$10:$F$16,$I$10:$I$16,"="&L22,$F$10:$F$16,">="&M22,$F$10:$F$16,"<="&N22)

Now drag the formula down, to extend it to K27

K22 to K27 will now show the miles for each group.

Regards
PS SUMIFS is not available in Excel 2003 or earlier, but other functions such as SUMPRODUCT could be used instead, if this workbook had to be run on Excel 2003 or earlier.


Report •

Related Solutions

#4
September 4, 2010 at 10:01:31
Humar, Thanks. I tried your formula but it didn't work. I don't understand where the L22, M22 & N22 values are coming from. I will try to show my spreadsheet below:

8 A B C D E F G H I J K
9 Dates Trip # Hrs Wrk Wait Tm KM Miles Drops Lyvr RT/V Wrk Type App
10 Au 1 01 0 0 800 497 4 0 RT R
11 Au 2 02 0 0 600 373 2 0 V R
12 Au 3 03 0 0 1000 622 4 0 RT R
13 Au 4 04 0 0 500 311 4 0 RT R
14 Au 5 05 0 0 700 435 2 0 V R
15 Au 6 06 0 0 900 559 4 0 RT R
16 Au 7 07 0 0 1200 746 2 0 V R
17
18
19
20 Regular Hours
21 Waiting Time
22 Mileage - Vans >500 miles
23 Mileage - Vans 425 - 500 mi
24 Mileage - Vans <425 miles
25 Mileage - Roll Tite >500 m
26 Mileage - Roll Tite 425 - 500 m
27 Mileage - Roll Tite <425 m
28 Drops/Picks - Vans
29 Drops/Picks - Roll Tites
30 Layovers

OK, A to K are the columns and their headings. 8 to 30 are the actual row numbers and the formulas that I am trying to create goes in K22 to K27. I should be able to then modify the formula to do a similar calculation for the Drops and Picks in K28 & K29. I hope this makes it more clear.

Unfortunately the message as I typed it is not formatted the same in this display.


Report •

#5
September 4, 2010 at 10:41:15
Humar, Thank you very much. I wasn't aware of the "SUMIFS" function but with your example I was able to figure out what was wrong and make it work. Thanks again, I couldn't have done it without your help.

Report •

#6
September 4, 2010 at 11:00:22
Hi,

EDIT - I see you just posted to say you've got it working - great to hear that.
So ignore the rest of this post, although you might look at the note at the end about posting data.

I don't understand where the L22, M22 & N22 values are coming from
I said in my response: I created a table of values to go into cells L23 to N27
Each row contained the data for the group, as follows:

This should have said L22 to N27

This table provides the source parameters for the formulas to work on.
500 & 9999 is just one way of identifying mileage of 500 miles and more.

By using a table, I was able to use one formula that could be dragged down in column K from K22 to K27.
Each formula could have been different - each containing V or RT and the mileage values such as >500.

The table approach just simplified the way the formulas were created.

Using the mileage and vehicle type data from your last post, (column F & I and rows 10 to 16, this is what I got:

	A			...	K
22	Mileage - Vans >500 miles	746
23	Mileage - Vans 425 - 500 mi	435
24	Mileage - Vans <425 miles	373
25	Mileage - Roll Tite >500 m	1181
26	Mileage - Roll Tite 425 - 500	497
27	Mileage - Roll Tite <425 m	311

Are these results correct?

Please, if posting data, to help line up columns, put your data between <pre> and </pre> tags that you can insert using the 'Pre' icon above the reply box. Then use the Preview button and edit as required. To preview again, check the 'Check To Show Confirmation Page Again' box and click 'Confirm and see post'

Regards


Report •

#7
September 5, 2010 at 14:54:47
Humar

Yes your results are correct. Thanks again for the help and for the info re posting data in columns.

rct


Report •


Ask Question