Help Editing Formula

Charlie · Mar 28th, 2002, 03:45 PM

Hi, I need help editing this formula =IF(OR(D9={"Paid in Full","Underpaid"}),0%,$E$3)the new formula has gotta add $E$4 representing a rise of 3% for Feb the $E$3 is 6% Jan, in short it has gotta change the data in the resulting column depending on whether the month is Jan or Feb.The column with Months in it is B8.
Thanks

[ This Message was edited by: Charlie on 2002-03-28 15:09 ]

Mark W. · Mar 28th, 2002, 03:59 PM

=IF(OR(D9={"Paid in Full","Underpaid"}),0,INDIRECT(VLOOKUP(B8,{"Jan","E3";"Feb","F4"},2,0)))

Aladin Akyurek · Mar 28th, 2002, 04:01 PM

Quote:

On 2002-03-28 14:45, Charlie wrote:
Hi, I need help editing this formula =IF(OR(D9={"Paid in Full","Underpaid"}),0%,$E$3)the new formula has gotta add $F$4 representing a rise of 3% for Feb the $E$3 is 6% Jan, in short it has gotta change the data in the resulting column depending on whether the month is Jan or Feb.The column with Months in it is B8.
Thanks

Perhaps:

=(D9={"Paid in Full","Underpaid"})*0%+(B8="Jan")*$E$3+(B8="Feb")*$F$4

Charlie · Mar 28th, 2002, 04:20 PM

Hmmm, the first reply posted when entered results in the data being reproduced in the cell, a long row of text.The second one goes in ok but when copied down the column the results are all 0%, I need to show 0% if Paid in Full or Underpaid 6% if Unpaid changing to 3% if the month is February.
Thanks
I made a slight error in the question, 3% is in cell E4 not F4, I tried the correction in both cases.

Aladin Akyurek · Mar 28th, 2002, 04:29 PM

Quote:

On 2002-03-28 15:20, Charlie wrote:
Hmmm, the first reply posted when entered results in the data being reproduced in the cell, a long row of text.The second one goes in ok but when copied down the column the results are all 0%, I need to show 0% if Paid in Full or Underpaid 6% if Unpaid changing to 3% if the month is February.
Thanks
I made a slight error in the question, 3% is in cell E4 not F4, I tried the correction in both cases.

=IF(OR(D9={"Paid in Full","Underpaid"}),0%,(B8="Jan")*$E$3+(B8="Feb")*$E$4)

Does this work?

Addendum: Make "Jan" "January" if "January" is what you have in B8.

[ This Message was edited by: Aladin Akyurek on 2002-03-28 15:30 ]

[ This Message was edited by: Aladin Akyurek on 2002-03-28 15:32 ]

[ This Message was edited by: Aladin Akyurek on 2002-03-28 15:38 ]

Charlie · Mar 28th, 2002, 04:35 PM

Changed it to January and February but the data is now showing the same as when I started 0% for Paid etc and 6% for unpaid
Charlie

[ This Message was edited by: Charlie on 2002-03-28 15:39 ]

Aladin Akyurek · Mar 28th, 2002, 04:42 PM

Quote:

On 2002-03-28 15:35, Charlie wrote:
It seems to work until copied down the column, all results are 0%.
I dont know how to solve this?
Charlie

Hard to answer such a question. Can you copy 10 rows of data and indicate where you put the formula?

Charlie · Mar 28th, 2002, 04:47 PM

Month Invoice Amount Comment Percentage New Amount
January £47.25 Paid in Full 0% £47.25
January £267.75 Underpaid 0% £267.75
January £47.25 Paid in Full 0% £47.25
January £472.50 Paid in Full 0% £472.50
January £456.75 Paid in Full 0% £456.75
January £126.00 Paid in Full 0% £126.00
January £456.75 Unpaid 6% £484.16
January £63.00 Paid in Full 0% £63.00
February £283.50 Unpaid 6% £300.51
January £283.50 Unpaid 6% £300.51
January £173.25 Underpaid 0% £173.25
January £267.75 Unpaid 6% £283.82
January £47.25 Paid in Full 0% £47.25
Chaotic I know.

[ This Message was edited by: Charlie on 2002-03-28 15:51 ]

Aladin Akyurek · Mar 28th, 2002, 04:53 PM

Quote:

On 2002-03-28 15:47, Charlie wrote:
Month Invoice Amount Comment Percentage New Amount
January £47.25 Paid in Full 0% £47.25
January £267.75 Underpaid 0% £267.75
January £47.25 Paid in Full 0% £47.25
January £472.50 Paid in Full 0% £472.50
January £456.75 Paid in Full 0% £456.75
January £126.00 Paid in Full 0% £126.00
January £456.75 Unpaid 6% £484.16
January £63.00 Paid in Full 0% £63.00
February £283.50 Unpaid 6% £300.51
January £283.50 Unpaid 6% £300.51
January £173.25 Underpaid 0% £173.25
January £267.75 Unpaid 6% £283.82
January £47.25 Paid in Full 0% £47.25
Chaotic I know.

[ This Message was edited by: Charlie on 2002-03-28 15:51 ]

Can you state the range of the data you provided? Is it for the percentage column that you need the formula?

Charlie · Mar 28th, 2002, 04:55 PM

Yes the data is to appear in the Percentages column.
Charlie

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 28th, 2002, 03:45 PM	#1
Charlie Board Regular Join Date: Mar 2002 Posts: 134	Hi, I need help editing this formula =IF(OR(D9={"Paid in Full","Underpaid"}),0%,$E$3)the new formula has gotta add $E$4 representing a rise of 3% for Feb the $E$3 is 6% Jan, in short it has gotta change the data in the resulting column depending on whether the month is Jan or Feb.The column with Months in it is B8. Thanks [ This Message was edited by: Charlie on 2002-03-28 15:09 ]

Mar 28th, 2002, 03:59 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=IF(OR(D9={"Paid in Full","Underpaid"}),0,INDIRECT(VLOOKUP(B8,{"Jan","E3";"Feb","F4"},2,0)))

Mar 28th, 2002, 04:20 PM	#4
Charlie Board Regular Join Date: Mar 2002 Posts: 134	Hmmm, the first reply posted when entered results in the data being reproduced in the cell, a long row of text.The second one goes in ok but when copied down the column the results are all 0%, I need to show 0% if Paid in Full or Underpaid 6% if Unpaid changing to 3% if the month is February. Thanks I made a slight error in the question, 3% is in cell E4 not F4, I tried the correction in both cases.

Mar 28th, 2002, 04:35 PM	#6
Charlie Board Regular Join Date: Mar 2002 Posts: 134	Changed it to January and February but the data is now showing the same as when I started 0% for Paid etc and 6% for unpaid Charlie [ This Message was edited by: Charlie on 2002-03-28 15:39 ]

Mar 28th, 2002, 04:47 PM	#8
Charlie Board Regular Join Date: Mar 2002 Posts: 134	Month Invoice Amount Comment Percentage New Amount January £47.25 Paid in Full 0% £47.25 January £267.75 Underpaid 0% £267.75 January £47.25 Paid in Full 0% £47.25 January £472.50 Paid in Full 0% £472.50 January £456.75 Paid in Full 0% £456.75 January £126.00 Paid in Full 0% £126.00 January £456.75 Unpaid 6% £484.16 January £63.00 Paid in Full 0% £63.00 February £283.50 Unpaid 6% £300.51 January £283.50 Unpaid 6% £300.51 January £173.25 Underpaid 0% £173.25 January £267.75 Unpaid 6% £283.82 January £47.25 Paid in Full 0% £47.25 Chaotic I know. [ This Message was edited by: Charlie on 2002-03-28 15:51 ]

Mar 28th, 2002, 04:55 PM	#10
Charlie Board Regular Join Date: Mar 2002 Posts: 134	Yes the data is to appear in the Percentages column. Charlie