Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating total when multiplier varies by another criteria
I think I can probably figure out a convoluted, around-the-world way to do
this, but there's GOT to be an easier way. Unfortunately, I neither know nor understand anything at all about macros or creating functions, and I'm afraid that's what I'm going to need. I'm trying to calculate the cost on forms we use. The base is .0135 for b&w and .07 for color if I go through the print shop, and .008 for b&w and .0613 for color if I print it myself. Then it changes based on the number of pages. I set my spreadsheet up with the following columns: Name - Color - B & W - Carbon - # of Pages - Print Shop - Self If I use the "Color" and "B & W" columns to just place an "X" in the cell to denote which it is, how can I calculate the cost for both the Print Shop and myself? Does that even make sense to anyone other than me? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating total when multiplier varies by another criteria
It's just a simple formula.
Specify number of colour & B&W pages then another column for you =(colourpages*youcost)+(B&Wpages*youcost) another column for printshop =(colourpages*pintshopcost)+(B&Wpages*printshopcos t) You may find that the printshop has a variable price depending on number of copies in which case all you need to do is specify different prices for different numbers eg if(copies1,000,0.0001,normal cost) which basically says if copies are above 1,000 use this price otherwise use normal price hope it helps doug "HeatherJG" wrote: I think I can probably figure out a convoluted, around-the-world way to do this, but there's GOT to be an easier way. Unfortunately, I neither know nor understand anything at all about macros or creating functions, and I'm afraid that's what I'm going to need. I'm trying to calculate the cost on forms we use. The base is .0135 for b&w and .07 for color if I go through the print shop, and .008 for b&w and .0613 for color if I print it myself. Then it changes based on the number of pages. I set my spreadsheet up with the following columns: Name - Color - B & W - Carbon - # of Pages - Print Shop - Self If I use the "Color" and "B & W" columns to just place an "X" in the cell to denote which it is, how can I calculate the cost for both the Print Shop and myself? Does that even make sense to anyone other than me? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating total when multiplier varies by another criteria
Assuming your data starts in Row 2 (with Row 1 being a header row), you can
calculate the cost per name (row) using this formula... =IF(A2="","",E2*IF(OR(AND(B2<"",C2<""),AND(F2<" ",G2<"")),"0",IF(B2<"",0.0613+0.0087*(F2<""),0. 008+0.0055*(F2<"")))) and copy it down for the other names (rows). If there is no entry in A2, an empty string is returned even if there are entries in the other cells on the row. If A2 is filled in, then the formula returns 0 if both B2 and C2 are not empty at the same time and 0 if both F2 and G2 are not both empty at the same time. You can use anything you want to select between Color and B/W and also between Self and Print Shop... you do not have to restrict yourself to an "X" unless you want to. Rick "HeatherJG" wrote in message ... I think I can probably figure out a convoluted, around-the-world way to do this, but there's GOT to be an easier way. Unfortunately, I neither know nor understand anything at all about macros or creating functions, and I'm afraid that's what I'm going to need. I'm trying to calculate the cost on forms we use. The base is .0135 for b&w and .07 for color if I go through the print shop, and .008 for b&w and .0613 for color if I print it myself. Then it changes based on the number of pages. I set my spreadsheet up with the following columns: Name - Color - B & W - Carbon - # of Pages - Print Shop - Self If I use the "Color" and "B & W" columns to just place an "X" in the cell to denote which it is, how can I calculate the cost for both the Print Shop and myself? Does that even make sense to anyone other than me? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating total when multiplier varies by another criteria
I guess I should have mentioned that when you posted this...
Name - Color - B & W - Carbon - # of Pages - Print Shop - Self I assumed it was meant to show the header text for Columns A through G inclusive... if your have those headings (and associated data) in different columns from those, you will have to adjust the cell references in my formula accordingly. Rick "Rick Rothstein (MVP - VB)" wrote in message ... Assuming your data starts in Row 2 (with Row 1 being a header row), you can calculate the cost per name (row) using this formula... =IF(A2="","",E2*IF(OR(AND(B2<"",C2<""),AND(F2<" ",G2<"")),"0",IF(B2<"",0.0613+0.0087*(F2<""),0. 008+0.0055*(F2<"")))) and copy it down for the other names (rows). If there is no entry in A2, an empty string is returned even if there are entries in the other cells on the row. If A2 is filled in, then the formula returns 0 if both B2 and C2 are not empty at the same time and 0 if both F2 and G2 are not both empty at the same time. You can use anything you want to select between Color and B/W and also between Self and Print Shop... you do not have to restrict yourself to an "X" unless you want to. Rick "HeatherJG" wrote in message ... I think I can probably figure out a convoluted, around-the-world way to do this, but there's GOT to be an easier way. Unfortunately, I neither know nor understand anything at all about macros or creating functions, and I'm afraid that's what I'm going to need. I'm trying to calculate the cost on forms we use. The base is .0135 for b&w and .07 for color if I go through the print shop, and .008 for b&w and .0613 for color if I print it myself. Then it changes based on the number of pages. I set my spreadsheet up with the following columns: Name - Color - B & W - Carbon - # of Pages - Print Shop - Self If I use the "Color" and "B & W" columns to just place an "X" in the cell to denote which it is, how can I calculate the cost for both the Print Shop and myself? Does that even make sense to anyone other than me? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating total in a range of numbers | Excel Discussion (Misc queries) | |||
Calculating total time | Excel Worksheet Functions | |||
calculating total time | Excel Discussion (Misc queries) | |||
Calculating Overtime from Hours total | Excel Worksheet Functions | |||
CALCULATING CELLS AS A TOTAL | Excel Discussion (Misc queries) |