IF and AND formulas not capturing specified criteria

[Apples52]

Hi, I have three different formulas I'm trying to set up and can't get any of them to work properly. Can someone help me where I'm going wrong?

In column P, I want to capture the value from column V if the account value in column S is between 40000 - 49999 or 50000-79999.

In column Q, I want to capture the value from column V if the account value in column S is between 50000-79999 or 90000-99999

In column R I want to caputre the value from column V if the account value in column S is less than 4000

My current formula in column P is =AND(T7<="49999")+AND(T7>="80000",T7<="89999")

My current formula in column Q is =AND(T7<="50000",T7>="79999")+AND(T7>="90000",T7<="99999")

My current forumla in column R is =IF(T7<"40000",V7,0). This forumal as written is picking up all account values, not just those under 40000

Also, any way to exclude the #VALUE! cells? Or make those cells 0?

 

[etaf]

In column P, I want to capture the value from column V if the account value in column S is between 40000 - 49999 or 50000-79999.

=OR(AND(S2>=40000,S2<=49999),AND(S2>=50000,S2<=79999))

=IF(OR(AND(S2>=40000,S2<=49999),AND(S2>=50000,S2<=79999)), V2, "")
does that work for 1st formula in column P

change the row reference to match your data
difficult to see the image

i dont understand the T7 reference, in your example as it does not match the text

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Apples52]

=OR(AND(S2>=40000,S2<=49999),AND(S2>=50000,S2<=79999))

=IF(OR(AND(S2>=40000,S2<=49999),AND(S2>=50000,S2<=79999)), V2, "")
does that work for 1st formula in column P

change the row reference to match your data
difficult to see the image

i dont understand the T7 reference, in your example as it does not match the text

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to ever

Oh thanks for that. I tried installing the add-in but ran into setting and security issues where it ultimately wouldn't let me capture into a mini sheet- the option was grayed out. I followed some help articles but nothing seemed to utimately work.

I put together a mock sheet with the formulas in columns P/Q/R that I still cant' get to work quite right. I've highlighted a note of what I'm trying to accomplish in each column.

Link to my google drive mock spreadsheet.
IF AND Help

 

[etaf]

so the issue is
=LEFT(A7,5)
in S7 as that will return a text value
and so all the results will be false and return a blank cell
to conver to a number
=LEFT(A7,5)*1
Now for things that return a text like total
capture the error using
=IFERROR(LEFT(A7,5)*1,0)

I have also instead of "" , used "not between values"
=IF(OR(AND(S7>=40000,S7<=49999),AND(S7>=80000,S7<=89999)), U7, "not between values")
so you can see when the FALSE is applied to the IF

linked to dropbox and put a section in xl2bb

dropbox will only be available for a few days

Dropbox

www.dropbox.com

IF AND Help.xlsx
	A	B	C												O	P	Q	R	S	T	U
1																Income	Expense	BS	Account	Description	Amount
2																If S is between 40000-49999 OR 80000-89999, display U	If S is between 50000-79999 OR 90000-99999, display U	If S is less than 40000, display U		0	-
3																				0	-
4																					-
5																					-
6	Account	Debit	Credit																Accou	Account	Credit
7	10200 - Cash On Hand	$20,000.00														not between values	not between values	0	10200	10200 - Cash On Hand	$20,000.00
8	10300 - Credit Card Clearing	$5,000.00														not between values	not between values	0	10300	10300 - Credit Card Clearing	$5,000.00
9	10320 - Credit Card Clearing	$2,500.00														not between values	not between values	0	10320	10320 - Credit Card Clearing	$2,500.00
10	10450 - Lottery Account	$20,337.75														not between values	not between values	0	10450	10450 - Lottery Account	$20,337.75
11	21200 - Credit Cards Payable -															not between values	not between values	0	21200	21200 - Credit Cards Payable -	$0.00
12	21201 - Credit Card		$3,132.00													not between values	not between values	0	21201	21201 - Credit Card	($3,132.00)
13	21231 - Credit Card		$789.00													not between values	not between values	0	21231	21231 - Credit Card	($789.00)
14	Total - 21200 - Credit Cards Payable	$0.00	$3,921.00													not between values	not between values	0	0	Total - 21200 - Credit Cards Payable	($3,921.00)
15	30000 - Shareholder Equity															not between values	not between values	0	30000	30000 - Shareholder Equity	$0.00
16	31000 - Equity		$1,500.00													not between values	not between values	0	31000	31000 - Equity	($1,500.00)
17	Total - 30000 - Shareholder Equity		$1,500.00													not between values	not between values	0	0	Total - 30000 - Shareholder Equity	($1,500.00)
18	39000 - Retained Earnings		$8,000.00													not between values	not between values	0	39000	39000 - Retained Earnings	($8,000.00)
19	40000 - Sales															0	not between values	0	40000	40000 - Sales	$0.00
20	40005 - Apples		$1,234.00													-1234	not between values	-1234	40005	40005 - Apples	($1,234.00)
21	40010 -Berries		$1,236.00													-1236	not between values	-1236	40010	40010 -Berries	($1,236.00)
22	40015 - Bakery		$1,899.00													-1899	not between values	-1899	40015	40015 - Bakery	($1,899.00)
23	40020 - Meat		$2,050.00													-2050	not between values	-2050	40020	40020 - Meat	($2,050.00)
24	Total - 40000 - Sales	$0.00	$6,419.00													not between values	not between values	0	0	Total - 40000 - Sales	($6,419.00)
25	50000 - Cost of Goods Sold															not between values	0	0	50000	50000 - Cost of Goods Sold	$0.00
26	50005 - Apples	$500.00														not between values	500	500	50005	50005 - Apples	$500.00
Income Stmt

Cell Formulas
Range	Formula
S2:S6	S2	=LEFT(A2,5)
T2:T26	T2	=A2
U2:U6	U2	=C2
P4:P6	P4	=IF(OR(AND(S4>=40000,S4<=49999),AND(S4>=80000,S4<=89999)), U4, "")
Q4:Q6	Q4	=IF(OR(AND(S4>=50000,S4<=79999),AND(S4>=90000,S4<=99999)), U4, "")
P7:P26	P7	=IF(OR(AND(S7>=40000,S7<=49999),AND(S7>=80000,S7<=89999)), U7, "not between values")
Q7:Q26	Q7	=IF(OR(AND(S7>=50000,S7<=79999),AND(S7>=90000,S7<=99999)), U7, "not between values")
R7:R26	R7	=IF(S7>40000,U7,0)
S7:S26	S7	=IFERROR(LEFT(A7,5)*1,0)
U7:U26	U7	=B7-C7
B14:C14	B14	=SUM(B12:B13)
C17	C17	=SUM(C16:C16,)
B24:C24	B24	=SUM(B20:B23)

 

[Apples52]

Thank you! This is great. One thing I didn't catch is I need to make the income column negatvie, so the values return back positive (or opposite sign as in column U). When I try to wrap the whole formula in column P in a *-1, it gives me an error. How would I go about making that change?

And last piece-its very small, but in the dropbox file, I updated the formula in column R so that it also does not return data if comun S is 0. When I carry that formula up, I get the #VALUE! error in R6 because S6 is text. Is there a simple way to fix that?

 

[etaf]

then you want to *-1 for U7 - not all the formula

=IF(OR(AND(S7>=40000,S7<=49999),AND(S7>=80000,S7<=89999)), U7*-1, "not between values")

does this work "
=If(S7=0,"",IF(S7>40000,U7,0))