| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
total the column anyway
I need a way to use my formula even when there is no discount.
I 45 Tuition $1596.00 46 Registration Fee $25.00 47 Textbook $75.00 48 Group Discount 5% 49 Total I45-(I45*I48)+I46+I47 Formula works, but what if there's no discount? If cell I48 is blank then I get #value. I want it to total my numbers even if there is no discount. Thanks for looking at this. Rob |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
total the column anyway
you could use an if statement:
=if(I48<"",I45-(I45*I48)+I46+I47,I45+I46+I47) "Robb27" wrote: I need a way to use my formula even when there is no discount. I 45 Tuition $1596.00 46 Registration Fee $25.00 47 Textbook $75.00 48 Group Discount 5% 49 Total I45-(I45*I48)+I46+I47 Formula works, but what if there's no discount? If cell I48 is blank then I get #value. I want it to total my numbers even if there is no discount. Thanks for looking at this. Rob |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
total the column anyway
Robb27 wrote...
I need a way to use my formula even when there is no discount. I 45 Tuition $1596.00 46 Registration Fee $25.00 47 Textbook $75.00 48 Group Discount 5% 49 Total I45-(I45*I48)+I46+I47 Formula works, but what if there's no discount? If cell I48 is blank then I get #value. I want it to total my numbers even if there is no discount. If I48 were truly blank, it'd be evaluated as if it were zero in your formula, so your formula wouldn't return #VALUE!. Obviously, it's not blank even if it may *appear* to be. Rewrite your I49 formula as =SUM(I45:I47,-N(I48)*I45) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
total the column anyway
Hi,
That formula still returns a #value. If I change the < to an = Then it works. But. It won't work if I have a discount. ? Rob "ansoriano1" wrote: you could use an if statement: =if(I48<"",I45-(I45*I48)+I46+I47,I45+I46+I47) "Robb27" wrote: I need a way to use my formula even when there is no discount. I 45 Tuition $1596.00 46 Registration Fee $25.00 47 Textbook $75.00 48 Group Discount 5% 49 Total I45-(I45*I48)+I46+I47 Formula works, but what if there's no discount? If cell I48 is blank then I get #value. I want it to total my numbers even if there is no discount. Thanks for looking at this. Rob |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
total the column anyway
Robb...If you need to print this calculation sheet, would you mind to type 0%
in order to complete this sheet....otherwise use the If's. "Robb27" wrote: I need a way to use my formula even when there is no discount. I 45 Tuition $1596.00 46 Registration Fee $25.00 47 Textbook $75.00 48 Group Discount 5% 49 Total I45-(I45*I48)+I46+I47 Formula works, but what if there's no discount? If cell I48 is blank then I get #value. I want it to total my numbers even if there is no discount. Thanks for looking at this. Rob |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
total the column anyway
Ok, got it. Stumbled on to it by accident (if there are any...grin)
I took a shot and put an equals sign before the < and Excel told me it was going to change the formula to this: IF(I48<="",I45-(I45*I48)+I46+I47,I45+I46+I47) and asked me if i wanted to keep it. I said, "sure, why not?" and it works. Imagine that. Thank you for giving me a push! "ansoriano1" wrote: you could use an if statement: =if(I48<"",I45-(I45*I48)+I46+I47,I45+I46+I47) "Robb27" wrote: I need a way to use my formula even when there is no discount. I 45 Tuition $1596.00 46 Registration Fee $25.00 47 Textbook $75.00 48 Group Discount 5% 49 Total I45-(I45*I48)+I46+I47 Formula works, but what if there's no discount? If cell I48 is blank then I get #value. I want it to total my numbers even if there is no discount. Thanks for looking at this. Rob |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
total the column anyway
Thanks Harlan. Tried yours too. Worked great.
Rob "Harlan Grove" wrote: Robb27 wrote... I need a way to use my formula even when there is no discount. I 45 Tuition $1596.00 46 Registration Fee $25.00 47 Textbook $75.00 48 Group Discount 5% 49 Total I45-(I45*I48)+I46+I47 Formula works, but what if there's no discount? If cell I48 is blank then I get #value. I want it to total my numbers even if there is no discount. If I48 were truly blank, it'd be evaluated as if it were zero in your formula, so your formula wouldn't return #VALUE!. Obviously, it's not blank even if it may *appear* to be. Rewrite your I49 formula as =SUM(I45:I47,-N(I48)*I45) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Column picked randomly with probability relative to number of entr | Excel Worksheet Functions | |||
| Conditional Format as a MACRO | Excel Worksheet Functions | |||
| Pivot table for reporting sales performance | Excel Discussion (Misc queries) | |||
| creating a bar graph | Excel Discussion (Misc queries) | |||
| match and count words | Excel Worksheet Functions | |||
Linear Mode
